How can I find which tables reference a given table in Oracle SQL Developer?

Question

In Oracle SQL Developer, if I'm viewing the information on a table, I can view the constraints, which let me see the foreign keys (and thus which tables are referenced by this table), and I can view the dependencies to see what packages and such reference the table. But I'm not sure how to find which tables reference the table.

For example, say I'm looking at the emp table. There is another table emp_dept which captures which employees work in which departments, which references the emp table through emp_id, the primary key of the emp table. Is there a way (through some UI element in the program, not through SQL) to find that the emp_dept table references the emp table, without me having to know that the emp_dept table exists?

Answer 1

You need to execute a query either manually or you can use other tools as for example PLSQL Developer can execute a query by using the development tool. 

Below SQL query can be used by PLSQL Developer:

select table_name, constraint_name, status, owner

from all_constraints

where r_owner = :r_owner

and constraint_type = 'R'

and r_constraint_name in

 (

   select constraint_name from all_constraints

   where constraint_type in ('P', 'U')

   and table_name = :r_table_name

   and owner = :r_owner

 )

order by table_name, constraint_name

In the above query r_owner is the schema, and r_table_name is the table for which we are looking for references.

Note: Here, all the names are case sensitive.

But, Oracle SQL Developers don't have the option to find which table reference a given table is having.