Find objects used by specific stored procedure in Oracle database

Article for: SQL Server Azure SQL Database IBM Db2

Query below return:

A. all Procedures and objects used by them accessible to the current user in Oracle database.

B. all Procedures and objects used by them in Oracle database.

Query

A. All Procedures and objects used by them accessible to the current user

select owner || '.' || name as function_name,
       referenced_owner || '.' || referenced_name as referenced_object,
       referenced_type
from sys.all_dependencies
where type = 'PROCEDURE'
      and name = 'procedure name' -- put your procedure name here
      --and referenced_owner = 'schema name'
      and referenced_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 referenced_object;

B. If you have privilege on dba_dependencies

select owner || '.' || name as function_name,
       referenced_owner || '.' || referenced_name as referenced_object,
       referenced_type
from sys.dba_dependencies
where type = 'PROCEDURE'
      and name = 'procedure name' -- put your procedure name here
      --and referenced_owner = 'schema name'
      and referenced_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 referenced_object;

Columns

  • procedure_name - provided procedure name with schema name
  • referenced_object - schema name and name of the referenced object
  • referenced_type - type of referenced object

Rows

  • One row represents one referenced object by provided procedure
  • Scope of rows: all objects that are used by procedure in database
  • Ordered by schema name and name of referenced object

Sample Result

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