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

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.

Output will look something like:


@fchild_tree
Enter the exact object name to find info about:  per_addresses

THE_OBJECT
----------------------------------------------------------------------------------------------------
SYNONYM   APPS.PER_ADDRESSES    92203
SYNONYM   PUBLIC.PER_ADDRESSES    85116
TABLE   HR.PER_ADDRESSES    38985

3 rows selected.

From above, enter the exact object_id:  38985

FINDING_ALL_CHILDREN_FOR
----------------------------------------------------------------------------------------------------
TABLE   HR.PER_ADDRESSES   38985

1 row selected.

CHILD_LVL_OBJID_ROWNUM
----------------------------------------------------------------------------------------------------
  SYNONYM  APPS.PER_ADDRESSES   1   92203   1
...

    PACKAGE  APPS.PER_ADD_SHD   2   245641   636
      SYNONYM  PUBLIC.PER_ADD_SHD   3   85764   637
      PACKAGE  APPS.PER_ADD_FLEX   3   245640   638
        SYNONYM  PUBLIC.PER_ADD_FLEX   4   85669   639
        PACKAGE BODY  APPS.PER_ADD_BUS   4   264010   640
        PACKAGE BODY  APPS.PER_ADD_FLEX   4   273034   641
      PACKAGE  APPS.PER_ADD_DEL   3   245643   642
        SYNONYM  PUBLIC.PER_ADD_DEL   4   85766   643
        PACKAGE BODY  APPS.HR_H2PI_PERSON_UPLOAD   4   255974   644
        PACKAGE BODY  APPS.GHR_CORR_CANC_SF52   4   267035   645
        PACKAGE BODY  APPS.PER_ADD_DEL   4   273032   646
        PACKAGE BODY  APPS.IRC_PURGE_OLD_DATA_PKG   4   719001   647
...

  TRIGGER  APPS.PERADDRESSES_488I_DYT   1   3259153   1323
  TRIGGER  APPS.PERADDRESSES_131U_DYT   1   3259155   1324

1176 rows selected.

———————–

Delving a little deeper, the output says that PACKAGE APPS.PER_ADD_SHD refers to PER_ADDRESSES. Looking at the source code for the package, we get:

Select text
from all_source
where name = rtrim(ltrim(upper( 'PER_ADD_SHD' )))
and type = 'PACKAGE BODY'
and UPPER(TEXT) LIKE '%PER_ADDRESSES%'
order by owner, name, line

TEXT
---------------------------------------------------------------------
If (p_constraint_name = 'PER_ADDRESSES_PK') Then
from per_addresses
from per_addresses
hr_utility.set_message_token('TABLE_NAME', 'per_addresses');

So, if you were creating objects, PER_ADDRESSES must be created first, then create, PACKAGE APPS.PER_ADD_SHD.

———————–

Script: fchild_tree.sql

/*

fchild_tree.sql

Script to find all the successive generations of children of an object
By Rodger Lepinsky

Will display for example:

Table
  View
    Package

Will show the owner.object_name, level from the child, object_id, and the rownum
on one line for sqlplus

If you prefer, change the output to:

select object_name
, the_level
, object_id
, the_rownum 

*/

accept ls_object_name prompt "Enter the exact object name to find info about:  " ;

select     object_type  ||  '   '  ||  owner || '.' || object_name   ||
               '    ' || object_id
          as the_object
from all_objects
where upper(object_name) = trim(upper('&ls_object_name'))
order by object_type, owner, object_name
/

accept ls_obj_id prompt "From above, enter the exact object_id:  " ;

select  ao.object_type || '   ' ||   ao.owner || '.' || ao.object_name ||
           '   '  || ao.object_id
      as Finding_all_children_for
FROM  all_objects  ao
where    ao.object_id   =  &ls_obj_id
/

select object_name
   || '   '    ||  the_level
   || '   '    ||  object_id
   || '   '    ||  the_rownum
    as  child_lvl_objid_rownum
from
   (
   select  object_name     as object_name
   , OBJECT_ID
   , the_level
   , min (the_rownum)  as the_rownum
   from
       (  /* By Rodger Lepinsky */
       SELECT     LPAD(' ', 2*(LEVEL-1)) || '  '  ||
       ao.OBJECT_type || '  '  ||  ao.owner || '.' ||
       ao.object_name  || '   ' || ao.OBJECT_ID || '  ' || rownum
                AS THE_TREE
       , LPAD ( ' ', 2*(LEVEL-1) ) || '  '  ||
         ao.OBJECT_type || '  ' ||
         ao.owner ||  '.' ||
         ao.object_name
              as object_name
       , ao.owner ||  '.' || ao.object_name
            as object_name2
       , ao.OBJECT_type
       , ao.OBJECT_ID
       , ao.owner
       , rownum as the_rownum
       , level as the_level
       FROM               PUBLIC_DEPENDENCY   pd
       join         all_objects  ao
            on    ao.object_id   = pd.object_id
/*       where ao.owner NOT in  ( 'SYS', 'SYSTEM', 'PUBLIC' )    */
       START WITH         pd.REFERENCED_OBJECT_ID =  &ls_obj_id
       CONNECT BY PRIOR   pd.OBJECT_ID = pd.REFERENCED_OBJECT_ID
      )
   group by object_name, object_id, the_level
   )
order by the_rownum
/

-----------------------
 The final output excludes objects owned by: SYS, SYSTEM, PUBLIC. UnComment that line if you want to include them. Although, it would be highly unusual to create a SYSTEM object on top of a regular schema object.

Note: it’s best to run this script as a user with the privilege: Select Any Table. Otherwise, you may not see all the objects.

To find the tree of all the -parents- of an object, see my previous post.

About these ads

One Response to Finding All Generations Of An Object’s Children

  1. rodgersnotes says:

    As you know, DBA_DEPENDENCIES only go one layer up or down.

    So, if you have multiple layers, you have to do recursive queries.

    I’ve since been looking at solving this problem using the graph database, Neo4J. See one initial article here:

    http://rodgersnotes.wordpress.com/2013/07/31/dba_objects-tree-modelled-as-a-graph/

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: