Oracle which tables are using given table as foreign key table.

With the following SQL-clause you can check which tables are using table that you put into SQL-clause as foreign key table:

SQL> select table_name, constraint_name, status, owner
from all_constraints
where r_owner = '<SCHEMA_NAME>'
and constraint_type = 'R'
and r_constraint_name in
 (
   select constraint_name from all_constraints
   where constraint_type in ('P', 'U')
   and table_name = '<TABLE_NAME>'
   and owner = '<SCHEMA_NAME>'
 )
order by table_name, constraint_name;

Example:

SQL> select table_name, constraint_name, status, owner
from all_constraints
where r_owner = 'TEST_USER'
and constraint_type = 'R'
and r_constraint_name in
 (
   select constraint_name from all_constraints
   where constraint_type in ('P', 'U')
   and table_name = 'TEST_TABLE1'
   and owner = 'TEST_USER'
 )
order by table_name, constraint_name;





TABLE_NAME                     CONSTRAINT_NAME                STATUS
------------------------------ ------------------------------ --------
OWNER
------------------------------
TEST_TABLE2                        FKEY_TEST_TABLE2_TEST_TYPE      ENABLED
TEST_USER