Find where specific table or view is used in Oracle database

Article for: SQL Server Azure SQL Database

Query below list

A. all objects where specific table or view accessible to the current user is used in Oracle database

B. all objects where specific table or view is used in Oracle database

Query

**A. all objects where specific table or view accessible to the current user is used **

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 = 'EMPLOYEES' -- 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;

B. 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 = 'EMPLOYEES' -- 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;

Columns

  • table_name - table/view name with schema name
  • type -
    • TABLE
    • VIEW
  • referencing_object - name of object which use specific table/view
  • referencing_type - type of found object

Rows

  • One row represents one object
  • Scope of rows: all objects that are using provided table/view
  • Ordered by schema name and name of object

Sample Results

0
There are no comments. Click here to write the first comment.