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

In Oracle SQL Developer, you can use the data dictionary views to find which tables reference a given table. The data dictionaries views, such as DBA_CONSTRAINTS, DBA_DEPENDENCIES, and ALL_CONSTRAINTS, contain information about the constraints and dependencies in the database.

One way to find the tables that reference a given table is by using the DBA_CONSTRAINTS view. The following query will return all the constraints that reference the given table:

SELECT owner, table_name, constraint_name

FROM dba_constraints

WHERE r_owner = '<schema_name>' AND r_table_name = '<table_name>'

You can replace the <schema_name> and <table_name> with the appropriate values for your database.

Another way is to use the DBA_DEPENDENCIES view; it will return all the dependencies on the given table.

SELECT name, type, referenced_name, referenced_type

FROM dba_dependencies

WHERE referenced_name = '<table_name>'

AND referenced_type = 'TABLE'

You can also use the ALL_CONSTRAINTS view, which is similar to DBA_CONSTRAINTS but only shows the constraints for the current user:

SELECT table_name, constraint_name

FROM all_constraints

WHERE r_constraint_name IN (

  SELECT constraint_name

  FROM all_constraints

  WHERE table_name = '<table_name>'

)

It's important to note that these queries will only show the constraints defined on the table, not the indexes, triggers, or other types of objects that may reference the table. These queries also depend on the user's permission, so you might need specific permissions to access the data dictionary views.