Using utldtree.sql and deptree fill to recursively see all objects that are dependent on a given object.

Using utldtree.sql and deptree_fill to recursively see all objects that are dependent on a given object.

• Read the utility's description on how to use the script

$ cat  /opt/oracle/product/11.1.0/db_1/rdbms/admin/utldtree.sql
Rem 
Rem $Header: utldtree.sql,v 1.2 1992/10/26 16:24:44 RKOOI Stab $ 
Rem 
Rem  Copyright (c) 1991 by Oracle Corporation 
Rem    NAME
Rem      deptree.sql - Show objects recursively dependent on given object
Rem    DESCRIPTION
Rem      This procedure, view and temp table will allow you to see all
Rem      objects that are (recursively) dependent on the given object.
Rem      Note: you will only see objects for which you have permission.
Rem      Examples:
Rem        execute deptree_fill('procedure', 'scott', 'billing');
Rem        select * from deptree order by seq#;
Rem
Rem        execute deptree_fill('table', 'scott', 'emp');
Rem        select * from deptree order by seq#;
Rem
Rem        execute deptree_fill('package body', 'scott', 'accts_payable');
Rem        select * from deptree order by seq#;
Rem
Rem        A prettier way to display this information than
Rem             select * from deptree order by seq#;
Rem        is
Rem             select * from ideptree;
Rem        This shows the dependency relationship via indenting.  Notice
Rem        that no order by clause is needed with ideptree.
Rem    RETURNS
Rem 

• Run to create necessary objects if not already done

SYS AS SQL> @$ORACLE_HOME/rdbms/admin/utldtree.sql

• Run deptree_fill.

SYS AS SYSDBA> execute deptree_fill('table','owner_aga','agg_table');

PL/SQL procedure successfully completed.


SYS AS SYSDBA> select * from ideptree;

DEPENDENCIES
--------------------------------------------------------------------------------
            SYNONYM MAGG.V_CLARIFY_CMBP
         SYNONYM MAGG.VW_AGGWVDETAILSDATA_RPT
      VIEW BTS_SST_ROA.VI17512730C
      VIEW BTS_SST_ROA.VI103118
      VIEW BTS_SST_ROA.VI153320
            MATERIALIZED VIEW MAGG.MTR_MV_ALIAS_LINK

References: http://docs.oracle.com/cd/B28359_01/server.111/b28318/dependencies.htm#CHDBEEGI