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

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_namefrom  all_constraints cinner join all_cons_columns dest_cc on  c.r_constraint_name = dest_cc.constraint_name  and c.r_owner = dest_cc.ownerinner join all_cons_columns src_cc on  c.constraint_name = src_cc.constraint_name  and c.owner = src_cc.ownerwhere  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).

I always look at the Foreign keys for the starting table and work my way back. The DB tools usually have a dependencies or constraints node. I know PL/SQL Developer has a way to see FK's, but it's been a while since I have used it, so I can't explain it...

just replace XXXXXXXXXXXX with a table name...

/* The following query lists all relationships */ select a.owner||'.'||a.table_name "Referenced Table",b.owner||'.'||b.table_name "Referenced by",b.constraint_name "Foreign Key"from all_constraints a, all_constraints b where b.constraint_type = 'R'and a.constraint_name = b.r_constraint_name and b.table_name='XXXXXXXXXXXX' -- Table name order by a.owner||'.'||a.table_name

I had a similar problem recently, but experienced soon, that finding the direct dependencies is not enough. So I wrote a query to show a tree of multilevel foreign key dependencies:

SELECT LPAD(' ',4*(LEVEL-1)) || table1 || ' <-- ' || table2 tables, table2_fkeyFROM  (SELECT a.table_name table1, b.table_name table2, b.constraint_name table2_fkey  FROM user_constraints a, user_constraints b   WHERE a.constraint_type IN('P', 'U')   AND b.constraint_type = 'R'   AND a.constraint_name = b.r_constraint_name   AND a.table_name != b.table_name  AND b.table_name <> 'MYTABLE')CONNECT BY PRIOR  table2 = table1 AND LEVEL <= 5START WITH table1 = 'MYTABLE';

It gives a result like this, when using SHIPMENT as MYTABLE in my database:

SHIPMENT <-- ADDRESSSHIPMENT <-- PACKING_LIST    PACKING_LIST <-- PACKING_LIST_DETAILS    PACKING_LIST <-- PACKING_UNIT        PACKING_UNIT <-- PACKING_LIST_ITEM    PACKING_LIST <-- PO_PACKING_LIST...