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

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

SQL> select dbac.table_name,
       dbac.constraint_name "Primary Constraint Name",
       r.constraint_name "Referenced Constraint Name"
from dba_constraints dbac,
     (select cons.constraint_name,
             cons.r_constraint_name,
             cons.table_name
      from dba_constraints cons
      where table_name='<TABLE_NAME>'
      and constraint_type='R') r
where dbac.constraint_name=r.r_constraint_name;

Example:

SQL> select dbac.table_name,
       dbac.constraint_name "Primary Constraint Name",
       r.constraint_name "Referenced Constraint Name"
from dba_constraints dbac,
     (select cons.constraint_name,
             cons.r_constraint_name,
             cons.table_name
      from dba_constraints cons
      where table_name='TEST_TABLE2'
      and constraint_type='R') r
where dbac.constraint_name=r.r_constraint_name;

TABLE_NAME                     Primary Constraint Name
------------------------------ ------------------------------
Referenced Constraint Name
------------------------------
TEST_TABLE1                   PKEY_TEST_TABLE1_TEST_TYPE
FKEY_TEST_TABLE2_TEST_TYPE