Complete (recursive) object dependency

Posted By Sagar Patil

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

Leave a Reply

You must be logged in to post a comment.

Top of Page

Top menu