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.

$ 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
 * Read the utility's description on how to use the script

SYS AS SQL> @$ORACLE_HOME/rdbms/admin/utldtree.sql
 * Run to create necessary objects if not already done

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

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