SELECT LPAD(' ', (A.NIVEL - 1) * 2) || OBJ.NAME A,
LPAD(' ', (A.NIVEL - 1) * 2) || CONS.NAME B
FROM SYS.OBJ$ OBJ,
SYS.CON$ CONS,
(SELECT OBJ# OBJ#, CON#, LEVEL NIVEL
FROM SYS.CDEF$
WHERE RCON# IS NOT NULL AND ROBJ# IS NOT NULL
CONNECT BY ROBJ# = PRIOR OBJ# AND ROBJ# != OBJ# AND
PRIOR ROBJ# != PRIOR OBJ#
START WITH ROBJ# = (SELECT OBJ#
FROM SYS.OBJ$
WHERE NAME = UPPER('fnd_concurrent_requests') AND TYPE# = 2 AND
OWNER# = USERENV('SCHEMAID'))) A
WHERE CONS.CON# = A.CON# AND OBJ.OBJ# = A.OBJ# AND OBJ.TYPE# = 2
UNION ALL
SELECT LPAD(' ', (A.NIVEL - 1) * 2) || OBJ.NAME A, TO_CHAR(NULL)
FROM SYS.OBJ$ OBJ,
(SELECT D_OBJ# OBJ#, LEVEL NIVEL
FROM SYS.DEPENDENCY$
CONNECT BY P_OBJ# = PRIOR D_OBJ#
START WITH P_OBJ# = (SELECT OBJ#
FROM SYS.OBJ$
WHERE NAME = UPPER('fnd_concurrent_requests') AND
OWNER# = USERENV('SCHEMAID'))) A
WHERE OBJ.OBJ# = A.OBJ# AND OBJ.TYPE# != 2
Is this the tree starting from the child, or the parent? What does the output look like?
ReplyDeleteI've written some scripts to do similar.
Starting from the child, finding all the parents:
http://rodgersnotes.wordpress.com/2011/12/29/the-parents-and-the-order-of-operations/
..but its not working when search for an object which exist with the same name for different types (type#) and with the same owner
ReplyDeletei.e Package (9) Package Body (11)
i.e Synonym (5) Table (2)
...with the synonyms I made a mistake -not possible to have them with the same name for the same owner (schema)....
Delete