dba_dependencies help

From: Steve Perry <sperry_at_sprynet.com>
Date: Sun, 25 Oct 1998 00:38:02 -0700
Message-ID: <3632D55A.785C18BD@sprynet.com>

I would like to use the dba_dependencies view to create a tree by schema showing dependencies. Because the "dba_" is a view that joins tables, I couldn't use it. I started looking at sys.dependency$. It still doesn't do exactly what I want. For instance, it will have some indentations that show dependencies for a view, but if a view depends on another table it doesn't show that dependency in the same grouping. It's above. If someone wants to fix this or send me some PL/SQL that could do it, it would be greatly appreciated.

Thanks,
Steve

SET PAGESIZE 30

COL P_NAME FORMAT A45 HEADING 'PARENT NAME'
COL D_NAME FORMAT A45 HEADING 'DEPENDENT NAME'
COL NAME   FORMAT A35 HEADING 'OBJECT NAME'

SELECT
       RPAD (' ', C.INDENTATION) || PD.NAME || '.' || B.NAME D_NAME
     , decode(B.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', 'UNDEFINED') TYPE
     , PA.NAME || '.' || A.NAME P_NAME
     , decode(A.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', 'UNDEFINED') TYPE

FROM SYS.OBJ$ A
, SYS.OBJ$ B
, (SELECT 2 * (level-1) INDENTATION

           , D_OWNER#
           , D_OBJ#
           , P_OBJ#
        FROM SYS.DEPENDENCY$
        WHERE P_OBJ# IN (SELECT OBJ# FROM SYS.OBJ$ WHERE OWNER# = 91)
          AND D_OWNER# = 91
        connect by prior D_OBJ# = P_OBJ#) C

, SYS.USER$ PA
, SYS.USER$ PD

WHERE A.OBJ# = C.P_OBJ#
  AND B.OBJ# = C.D_OBJ#
  and A.owner# = PA.user#
  and B.owner# = PD.user#
  AND A.TYPE != 10
  AND B.TYPE != 10

/ Received on Sun Oct 25 1998 - 01:38:02 CST