ORA-02449: unique/primary keys in table referenced by foreign keys

How to find foreign-key dependencies pointing to one record in Oracle?When you are getting below piece of error message, then you may has issue with dependent tables.

ORA-02449: unique/primary keys in table referenced by foreign keys

select src_cc.owner       as src_owner,
       src_cc.table_name  as src_table,
       src_cc.column_name as src_column,
       dest_cc.owner       as dest_owner,
       dest_cc.table_name  as dest_table,
       dest_cc.column_name as dest_column,
       c.constraint_name
from all_constraints c
inner join all_cons_columns dest_cc on c.r_constraint_name = dest_cc.constraint_name
                                    and c.r_owner = dest_cc.owner
inner join all_cons_columns src_cc on c.constraint_name =  src_cc.constraint_name
                                   and c.owner = src_cc.owner
where c.constraint_type = 'R'
/*and dest_cc.owner = 'MY_TARGET_SCHEMA'*/
and dest_cc.table_name = 'MY_TABLE_NAME'
/* and dest_cc.column_name = 'MY_OPTIONNAL_TARGET_COLUMN' */

Note: Put your owner name & column name.

Disable foreign key constraint may not help you if you are going to drop and re-create the tables for any maintenance activities. The above query will help you find any dependent table.