Complete (recursive) object dependency

The easiest approach is to use an Oracle utldtree.sql script. This will be located in the %ORACLE_HOME%\rdbms\admin\ directory.

For each schema that you want to be able to get a dependency list, you will open up a SQL*Plus session and execute the script
scott@> @d:\oracle\ora92\rdbms\admin\utldtree.sql
Don’t worry about the ORA-00942 errors you will get– this script tries to drop objects before it re-creates them, so you’ll get errors when the objects don’t exist yet.
Once the script has been run, you can get a listing of dependent objects for a particular object by calling

scott@jcave > exec deptree_fill('table', 'scott', 'emp' );

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

scott@jcave > select * from ideptree;

DEPENDENCIES

-------------------------------------------------------------------------------- 

TABLE SCOTT.EMP

   VIEW SCOTT.V2

   PROCEDURE SCOTT.INCREASE_SALARY

   VIEW SCOTT.V3

   PROCEDURE SCOTT.CALL_ME

   TRIGGER SCOTT.ODBC_EMP_TST

   <no>

7 rows selected.

Elapsed: 00:00:08.03