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.
