Wednesday, 4 June 2014

Oracle Dependency Tree structure

/* Formatted on 6/4/2014 2:26:23 PM (QP5 v5.115.810.9015) */
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

No comments:

Post a Comment