How to find direct and indirect dependencies between Oracle objects?

Hi all ! One of the biggest hassles I had a couple of months ago was how to find dependencies between Oracle objects. As always, after some internet browsing I found out that there was something to help us!

Before I go into further details let me just briefly explain the difference between direct and indirect dependencies. A direct dependency occurs for example when a view references a table . If the user drops the table, the view will no longer work(will be decompiled!). An indirect dependency occurs when a procedure references a view which is already referencing a table.  If the user drops the table or changes a referenced column, the view will be decompiled and the procedure who references the view will also be decompiled. The image below tries to show these concepts.

If you want to now more about valid and invalid object dependencies try this great article of Oracle. After understanding the theory of what I was speaking, let’s go now to the practice !

One can display both direct and indirect dependencies using deptree and ideptree views that are provided by default by Oracle database. To use these views follow the following steps :

  1. Look for utldtree.sql script on your <oracle_home>rdbmsadmin
  2. Run this script either in SQL Plus or the command line from PLSQL Developer. In case you are wondering to which directory do these command lines point to, look no further ! They point to dbs so just copy the script to this directory. Don’t run this script as sys user. Run it with your current user. I tried this with HR user.
  3. Execute the script with the command @utldtree.sql . This will create the structures needed for us query the views.
  4. Lets now populate the views with the info we want. There are several syntax possible. The deptree_fill procedure just created receives the following arguments (‘object type’, ‘object owner’, object name’). Object name is the name of the referenced object whose references we wish to discover. To use this procedure on the command window type : execute deptree_fill(‘table’, ‘HR’, ’employees’);
  5. Now just query the views, either deptree or ideptree with
 select * from ideptree 


 select * from deptree 

As you have noticed, the difference between these views is that the i in ideptree stands for indented!

That’s it ! Hope you enjoyed !