Re: dba_dependencies help

Sorry, but i don't know the correct statement. If you are satisfied with the result look at fttp:\\www.keeptool.com and download a free trial version of Hora 3. Hora shows the dependencies as tree view.

regards Jan Dieckmann

Steve Perry wrote:

> 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 Mon Oct 26 1998 - 08:36:04 CST