Notes on DEPTREE

After writing my own scripts to find either all parents or children of an object, I took a look at Oracle’s solution to find dependencies:

$ORACLE_HOME/rdbms/admin/utldtree.sql

It was actually written about 20 years ago, in 1991:
Rem rkooi 10/19/91 – Creation

Running the script, creates a number of objects:

OBJECT_AND_OBJECT_ID
----------------------------------------------------------------------------------------------------
PROCEDURE   SYS.DEPTREE_FILL    3304377
SEQUENCE   SYS.DEPTREE_SEQ    3304375
TABLE   SYS.DEPTREE_TEMPTAB    3304376
VIEW   SYS.DEPTREE    3304378
VIEW   SYS.IDEPTREE    3304379

7 rows selected.

 

Finding All Generations Of An Object’s Children

Say you have an object, such as a table. What are all objects are dependent on it? That is, all generations. Children, Grandchildren. Great-Grandchildren. Great-Great-Grandchildren. Etc. ??

Four Generations

If you drop that object, all generations of its children will become invalid.

The classic case is a table. There are generations of objects that are built referencing them.

Table
  View
    Package
      Function

Table
  Trigger

etc.

This script will be useful for:
– Determining the Order Of Operations when building objects.
– Determining Multiple dependencies
– Assessing the impact of changing a table structure, or dropping a table.

The Parents And The Order Of Operations

When you build objects in a new environment, you need to build them in the right order of operations.

Until you have all the objects in place, you can’t create a procedure that references them all. And if those objects require more parent objects, they must be created first too. For example:

procedure reads
    view which reads a
       table which is composed of a
          type

—-

Recently I wrote in my other post about the parent and child dependencies. They give a lot of great information. But they only go one level in either direction. As you know, there can be many levels of objects.

What is the order of operations to build them? I’ve written some complex scripts here to find all the successive parents of an object.