Find where specific function is used in Oracle database

Article for: SQL Server Azure SQL Database MySQL

Query below lists:

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

B.all objects where specific function is used in Oracle database.

Query

A. All objects where specific function is used accessible to the current user

select referenced_owner || '.' || referenced_name as function_name,
       owner || '.' || name as referencing_object,
       type
from sys.all_dependencies
where referenced_type = 'FUNCTION'
      and referenced_name = 'function name' -- put your function 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 privilege on dba_dependencies

select referenced_owner || '.' || referenced_name as function_name,
       owner || '.' || name as referencing_object,
       type
from sys.dba_dependencies
where referenced_type = 'FUNCTION'
      and referenced_name = 'function name' -- put your function 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

  • function_name - function name
  • referencing_object - schema name and name of object which use function
  • referenced_type - type of found object

Rows

  • One row represents one referencing object to provided function
  • Scope of rows: all objects that are using function in database
  • Ordered by schema name and name of the referencing object

Sample Result

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