Digging Deeper into DEPTREE_TEMPTAB

I had previously written about running a script to set up the DEPTREE_TEMPTAB table in my schema. I knew it was used to track dependencies. But I knew little else. Today I started calling the DEPTREE_FILL procedure to populate the table with objects I specified as parameters. However I only found the results for the latest procedure call in the table. Turns out each call truncates the results from prior calls.

The normal views like USER_DEPENDENCIES will show dependencies for multiple objects. However it only goes from the objects of interest to the direct objects they depend on. If those dependent objects themselves depend on other objects, that information is not captured in USER_DEPENDENCIES.

DEPTREE_TEMPTAB will dig deep and find direct and indirect dependencies for the object that you pass to DEPTREE_FILL. It produces records with the OBJECT_ID values for objects in the dependency chain, plus how deep they are in their level of indirection.

There is also an IDEPTREE view created when you run the utldtree SQL script to set all this up. This view shows the data from DEPTREE_TEMPTAB in a format that is more intuitive to the eye. Just like DEPTREE_TEMPTAB, the IDEPTREE view is not available with the default Oracle installation. You need to run a script to create it in your schema.

DEPTREE_TEMPTAB

I was taking a practice exam for an Oracle certification. There was a question about where I would look to track dependencies. The USER_DEPENDENCIES view was a given. But there seemed to be some other location. The answer given was table DEPTREE_TEMPTAB. I have never heard of it.

So I logged into Oracle and tried to describe DEPTREE_TEMPTAB. No such table. The first hit from Google search was from some Oracle 7 documentation. Maybe this table is long since deprecated? Nope. It does not come by default.

I located "utldtree.sql" script in my $ORACLE_HOME/rdbms/admin folder. It seems like I can run it from my own schema. There were some errors when I ran the script. But it was just some DROP commands that assumed the script was run before. Now I was off to the races:

SQL> desc deptree_temptab
 Name                                      Null?    Type
 ----------------------------------------- -------- ------

 OBJECT_ID                                          NUMBER
 REFERENCED_OBJECT_ID                               NUMBER
 NEST_LEVEL                                         NUMBER
 SEQ#                                               NUMBER

The table starts out empty. The comments in the script tell you to run a procedure to populate the table:

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#;

Now we are cooking with gas:

SQL> select * from deptree_temptab;

 OBJECT_ID REFERENCED_OBJECT_ID NEST_LEVEL       SEQ#
---------- -------------------- ---------- ----------
     89390                    0          0          0 

I will need to delve into loading the table up with a lot of objects that depend on others. Maybe that is a topic for a future post.