Find objects used by specific function in Oracle database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-06-04

Table of Contents:


    Article for: Oracle database SQL Server Azure SQL Database IBM Db2

    Query below return:

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

    B. all User Defined Functions and objects used by them in Oracle database.

    Query

    A. All User Defined Functions 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 = 'FUNCTION'
          and name = 'function_name' -- put your function 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 = 'FUNCTION'
          and name = 'function_name' -- put your function 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

    • function_name - function 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 function
    • Scope of rows: all objects that are used by function 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.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy (updated 28-04-2020).
    Accept