How to find foreign-key dependencies pointing to one record in Oracle?

Question

I have a very large Oracle database, with many many tables and millions of rows. I need to delete one of them, but want to make sure that dropping it will not break any other dependent rows that point to it as a foreign key record. Is there a way to get a list of all the other records, or at least table schemas, that point to this row? I know that I could just try to delete it myself, and catch the exception, but I won't be running the script myself and need it to run clean the first time through.

I have the tools SQL Developer from Oracle, and PL/SQL Developer from AllRoundAutomations at my disposal.

Thanks in advance!

Answer 1

Here is my solution to list all references to a table:

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_TARGET_TABLE'   --and dest_cc.column_name = 'MY_OPTIONNAL_TARGET_COLUMN' ; 

With this solution you also have the information of which column of which table is referencing which column of your target table (and you can filter on it).