oracle - Script to find multi level dependencies of a package

Question

I've a package which references many objects from the same schema and other schemas. I want to find all the dependencies of the package. I can get only first level dependencies from user_dependencies. Also utldtree would give me the objects which are dependent on my current object.utldtree also gives only the referenced objects in the same schema.

While I'm trying to find the solution for this on the net, I came across the following link http://rodgersnotes.wordpress.com/2012/01/05/notes-on-deptree/ where he mentioned that, he uses his own script to find the multi level dependencies of an object.

Could you please help me out, how to proceed for such a script which will get us the multi-level dependencies of an object,(for example if the package is referencing views, then our script should mention the views and the tables/views upon which our view is build as we get in deptree)

Answer 1

You can use a connect by on user_dependencies for most cases.

Determining dependencies

Sample which works for any Oracle user since PUBLIC has been granted select access on user_dependencies:

select name
,      type
,      prior name
,      prior type
from   user_dependencies
start 
with   name='BUBS#MUNT_EENHEDEN'
and    type='PACKAGE'
connect 
by     nocycle 
       name = prior referenced_name
and    type = prior referenced_type

Sample output

Level 1: BUBS#MUNT_EENHEDEN   PACKAGE
Level 2: BUBS_MUNT_EENHEDEN_V VIEW    BUBS#MUNT_EENHEDEN    PACKAGE
Level 3: BUBS#VERTALINGEN     PACKAGE BUBS_MUNT_EENHEDEN_V  VIEW
Level 4: ITGEN_LANGUAGES_V    VIEW    BUBS#VERTALINGEN      PACKAGE

Complex scenarios

For complex scenarios I've found it necessary to use an own view directly on the data dictionary. Do this only when you know what you are doing and what RDBMS version you want to support! For instance, datamodel versions introduced major changes in the data dictionary.

Sample:

create or replace force view itgen_object_tree_changes_r
as 
select o_master.obj#        ojt#
,      o_master.name        ojt_name
,      o.mtime              ojt_ref_mtime
,      o.name               ojt_ref_name
,      o.owner#             ojt_ref_owner#
,      decode
       ( o.type#
       , 0, 'NEXT OBJECT'
       , 1, 'INDEX'
       , 2, 'TABLE'
       , 3, 'CLUSTER'
       , 4, 'VIEW'
       , 5, 'SYNONYM'
       , 6, 'SEQUENCE'
       , 7, 'PROCEDURE'
       , 8, 'FUNCTION'
       , 9, 'PACKAGE'
       , 11, 'PACKAGE BODY'
       , 12, 'TRIGGER'
       , 13, 'TYPE'
       , 14, 'TYPE BODY'
       , 19, 'TABLE PARTITION'
       , 20, 'INDEX PARTITION'
       , 21, 'LOB'
       , 22, 'LIBRARY'
       , 23, 'DIRECTORY'
       , 24, 'QUEUE'
       , 28, 'JAVA SOURCE'
       , 29, 'JAVA CLASS'
       , 30, 'JAVA RESOURCE'
       , 32, 'INDEXTYPE'
       , 33, 'OPERATOR'
       , 34, 'TABLE SUBPARTITION'
       , 35, 'INDEX SUBPARTITION'
       , 40, 'LOB PARTITION'
       , 41, 'LOB SUBPARTITION'
       , 42, nvl
       ( ( select 'REWRITE EQUIVALENCE'
       from    sys.sum$ s
       where   s.obj# = o.obj#
       and     bitand ( s.xpflags, 8388608 ) = 8388608 ), 'MATERIALIZED VIEW'
       )
       , 43, 'DIMENSION'
       , 44, 'CONTEXT'
       , 46, 'RULE SET'
       , 47, 'RESOURCE PLAN'
       , 48, 'CONSUMER GROUP'
       , 51, 'SUBSCRIPTION'
       , 52, 'LOCATION'
       , 55, 'XML SCHEMA'
       , 56, 'JAVA DATA'
       , 57, 'EDITION'
       , 59, 'RULE'
       , 60, 'CAPTURE'
       , 61, 'APPLY'
       , 62, 'EVALUATION CONTEXT'
       , 66, 'JOB'
       , 67, 'PROGRAM'
       , 68, 'JOB CLASS'
       , 69, 'WINDOW'
       , 72, 'WINDOW GROUP'
       , 74, 'SCHEDULE'
       , 79, 'CHAIN'
       , 81, 'FILE GROUP'
       , 82, 'MINING MODEL'
       , 87, 'ASSEMBLY'
       , 90, 'CREDENTIAL'
       , 92, 'CUBE DIMENSION'
       , 93, 'CUBE'
       , 94, 'MEASURE FOLDER'
       , 95, 'CUBE BUILD PROCESS'
       , 'UNDEFINED'
       )
       ojt_ref_type
from   sys.obj$ o
,      ( /* All dependencies from the object if there are any. */
                  select distinct connect_by_root d_obj# obj#, dep.p_obj# obj_ref#
         from   sys.dependency$ dep
         connect
         by     nocycle dep.d_obj# = prior dep.p_obj#
         start
         with   dep.d_obj# in ( select obj.obj# from itgen_schemas_r sma, sys.obj$ obj where obj.owner# = sma.owner# )
         union all /* Union all allowed, 'in' ignores duplicates. */
         /* The object itself. */
         select obj.obj#
         ,      obj.obj#
         from   itgen_schemas_r sma
         ,      sys.obj$ obj
         where  obj.owner# = sma.owner#
       ) deps
,      sys.obj$ o_master
where  o_master.obj# = deps.obj#
and    o.obj# = deps.obj_ref#
--
-- View: itgen_object_tree_changes_r
--
-- Overview of dependencies between a master object and all objects used by it. It can be used to analyze the reason why a project version views must be recalculated.
--
-- Code (alias): ote_r
--
-- Category: Hardcoded.
--
-- Example:
--
-- The object 'X' is invalid, since 'Y' is invalid.
--