Find Where Specific Table or View is Used in Oracle Database

Here I am giving an example to find where specific table or view is used in oracle database

Query 1: All objects where specific table or view accessible to the current user is used in Oracle database.

SELECT referenced_owner

       || '.'

       || referenced_name AS table_name,

       referenced_type    AS TYPE,

       owner

       || '.'

       || NAME AS referencing_object,

       TYPE    AS referencing_type

  FROM sys.all_dependencies

 WHERE referenced_type IN ('TABLE', 'VIEW')

   AND referenced_name    = 'FXGN_USERS' -- put your table/view name here

   --AND referenced_owner = 'SCHEMA_NAME'

   AND owner NOT IN ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 

                     'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS',

                     'ORDPLUGINS', 'ORDSYS', 'SI_INFORMTN_SCHEMA',

                     'SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST', 

                     'WKPROXY','WMSYS','XDB','APEX_040000', 

                     'APEX_PUBLIC_USER','DIP', 'FLOWS_30000',

                     'FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL', 

                     'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 

                     'PUBLIC', 'OUTLN', 'WKSYS', 'LBACSYS')

 ORDER BY referencing_object;

Query 2: All objects where specific table or view is used in Oracle database (If you have privileges on dba_dependencies)

SELECT referenced_owner

       || '.'

       || referenced_name AS table_name,

       referenced_type    AS TYPE,

       owner

       || '.'

       || NAME AS referencing_object,

       TYPE    AS referencing_type

  FROM sys.dba_dependencies

 WHERE referenced_type IN ('TABLE', 'VIEW')

   AND referenced_name    = 'FXGN_USERS' -- put your table/view name here

   --AND referenced_owner = 'SCHEMA_NAME'

   AND owner NOT IN ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 

                     'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS',

                     'ORDPLUGINS', 'ORDSYS', 'SI_INFORMTN_SCHEMA',

                     'SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST', 

                     'WKPROXY','WMSYS','XDB','APEX_040000', 

                     'APEX_PUBLIC_USER','DIP', 'FLOWS_30000',

                     'FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL', 

                     'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 

                     'PUBLIC', 'OUTLN', 'WKSYS', 'LBACSYS')

 ORDER BY referencing_object;

Output:

Reference:

• ALL_OBJECTS
• DATAEDO

That's it.

Happy APEXing!!!...