VIEW: SYS.ALL_DEPENDENCIES

Source

View Text - Preformatted

select u.name, o.name,
       decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
                      4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE',
                      8, 'FUNCTION', 9, 'PACKAGE', 10, 'NON-EXISTENT',
                      11, 'PACKAGE BODY', 12, 'TRIGGER',
                      13, 'TYPE', 14, 'TYPE BODY', 22, 'LIBRARY',
                      23, 'DIRECTORY',
                      28, 'JAVA SOURCE', 29, 'JAVA CLASS',
                      32, 'INDEXTYPE', 33, 'OPERATOR',
                      42, 'MATERIALIZED VIEW', 43, 'DIMENSION',
                      46, 'RULE SET', 55, 'XML SCHEMA', 56, 'JAVA DATA',
                      59, 'RULE', 62, 'EVALUATION CONTXT', 87, 'ASSEMBLY',
                      90, 'CREDENTIAL',
                      92, 'CUBE DIMENSION', 93, 'CUBE',
                      94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS',
                      'UNDEFINED'),
       decode(po.linkname, null, pu.name, po.remoteowner), po.name,
       decode(po.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
                      4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE',
                      8, 'FUNCTION', 9, 'PACKAGE', 10, 'NON-EXISTENT',
                      11, 'PACKAGE BODY', 12, 'TRIGGER',
                      13, 'TYPE', 14, 'TYPE BODY', 22, 'LIBRARY',
                      23, 'DIRECTORY',
                      28, 'JAVA SOURCE', 29, 'JAVA CLASS',
                      32, 'INDEXTYPE', 33, 'OPERATOR',
                      42, 'MATERIALIZED VIEW', 43, 'DIMENSION',
                      46, 'RULE SET', 55, 'XML SCHEMA', 56, 'JAVA DATA',
                      59, 'RULE', 62, 'EVALUATION CONTXT', 87, 'ASSEMBLY',
                      90, 'CREDENTIAL',
                      92, 'CUBE DIMENSION', 93, 'CUBE',
                      94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS',
                      'UNDEFINED'),
       po.linkname,
       decode(bitand(d.property, 3), 2, 'REF', 'HARD')
from sys."_CURRENT_EDITION_OBJ" o, sys.disk_and_fixed_objects po, sys.dependency$ d, sys.user$ u,
  sys.user$ pu
where o.obj# = d.d_obj#
  and o.owner# = u.user#
  and po.obj# = d.p_obj#
  and po.owner# = pu.user#
  and
  (
    o.owner# in (userenv('SCHEMAID'), 1 /* PUBLIC */)
    or
    (
      (
         (
          (o.type# = 7 or o.type# = 8 or o.type# = 9 or
           o.type# = 28 or o.type# = 29 or o.type# = 56)
          and
          o.obj# in (select obj# from sys.objauth$
                     where grantee# in (select kzsrorol from x$kzsro)
                       and privilege#  = 12 /* EXECUTE */)
        )
        or
        (
          o.type# = 4
          and
          o.obj# in (select obj# from sys.objauth$
                     where grantee# in (select kzsrorol from x$kzsro)
                       and privilege# in (3 /* DELETE */,   6 /* INSERT */,
                                                7 /* LOCK */,     9 /* SELECT */,
                                          10 /* UPDATE */, 17 /* READ */))
        )
        or
        exists
        (
          select null from sys.sysauth$
          where grantee# in (select kzsrorol from x$kzsro)
          and
          (
            (
              /* procedure */
              (o.type# = 7 or o.type# = 8 or o.type# = 9 or
               o.type# = 28 or o.type# = 29 or o.type# = 56)
              and
              (
                privilege# = -144 /* EXECUTE ANY PROCEDURE */
                or
                privilege# = -141 /* CREATE ANY PROCEDURE */
              )
            )
            or
            (
              /* trigger */
              o.type# = 12 and
              privilege# = -152 /* CREATE ANY TRIGGER */
            )
            or
            (
              /* package body */
              o.type# = 11 and
              privilege# = -141 /* CREATE ANY PROCEDURE */
            )
            or
            (
              /* view */
              o.type# = 4
              and
              (
                privilege# in     ( -91 /* CREATE ANY VIEW */,
                                    -45 /* LOCK ANY TABLE */,
                                    -47 /* SELECT ANY TABLE */,
                                    -397/* READ ANY TABLE */,
                                    -48 /* INSERT ANY TABLE */,
                                    -49 /* UPDATE ANY TABLE */,
                                    -50 /* DELETE ANY TABLE */)
              )
            )
            or
            (
              /* type */
              o.type# = 13
              and
              (
                privilege# = -184 /* EXECUTE ANY TYPE */
                or
                privilege# = -181 /* CREATE ANY TYPE */
              )
            )
            or
            (
              /* type body */
              o.type# = 14 and
              privilege# = -181 /* CREATE ANY TYPE */
            )
          )
        )
      )
    )
    /* don't worry about tables, sequences, synonyms since they cannot */
    /* depend on anything */
  )

View Text - HTML Formatted

SELECT U.NAME
, O.NAME
, DECODE(O.TYPE#
, 0
, 'NEXT OBJECT'
, 1
, 'INDEX'
, 2
, 'TABLE'
, 3
, 'CLUSTER'
, 4
, 'VIEW'
, 5
, 'SYNONYM'
, 6
, 'SEQUENCE'
, 7
, 'PROCEDURE'
, 8
, 'FUNCTION'
, 9
, 'PACKAGE'
, 10
, 'NON-EXISTENT'
, 11
, 'PACKAGE BODY'
, 12
, 'TRIGGER'
, 13
, 'TYPE'
, 14
, 'TYPE BODY'
, 22
, 'LIBRARY'
, 23
, 'DIRECTORY'
, 28
, 'JAVA SOURCE'
, 29
, 'JAVA CLASS'
, 32
, 'INDEXTYPE'
, 33
, 'OPERATOR'
, 42
, 'MATERIALIZED VIEW'
, 43
, 'DIMENSION'
, 46
, 'RULE SET'
, 55
, 'XML SCHEMA'
, 56
, 'JAVA DATA'
, 59
, 'RULE'
, 62
, 'EVALUATION CONTXT'
, 87
, 'ASSEMBLY'
, 90
, 'CREDENTIAL'
, 92
, 'CUBE DIMENSION'
, 93
, 'CUBE'
, 94
, 'MEASURE FOLDER'
, 95
, 'CUBE BUILD PROCESS'
, 'UNDEFINED')
, DECODE(PO.LINKNAME
, NULL
, PU.NAME
, PO.REMOTEOWNER)
, PO.NAME
, DECODE(PO.TYPE#
, 0
, 'NEXT OBJECT'
, 1
, 'INDEX'
, 2
, 'TABLE'
, 3
, 'CLUSTER'
, 4
, 'VIEW'
, 5
, 'SYNONYM'
, 6
, 'SEQUENCE'
, 7
, 'PROCEDURE'
, 8
, 'FUNCTION'
, 9
, 'PACKAGE'
, 10
, 'NON-EXISTENT'
, 11
, 'PACKAGE BODY'
, 12
, 'TRIGGER'
, 13
, 'TYPE'
, 14
, 'TYPE BODY'
, 22
, 'LIBRARY'
, 23
, 'DIRECTORY'
, 28
, 'JAVA SOURCE'
, 29
, 'JAVA CLASS'
, 32
, 'INDEXTYPE'
, 33
, 'OPERATOR'
, 42
, 'MATERIALIZED VIEW'
, 43
, 'DIMENSION'
, 46
, 'RULE SET'
, 55
, 'XML SCHEMA'
, 56
, 'JAVA DATA'
, 59
, 'RULE'
, 62
, 'EVALUATION CONTXT'
, 87
, 'ASSEMBLY'
, 90
, 'CREDENTIAL'
, 92
, 'CUBE DIMENSION'
, 93
, 'CUBE'
, 94
, 'MEASURE FOLDER'
, 95
, 'CUBE BUILD PROCESS'
, 'UNDEFINED')
, PO.LINKNAME
, DECODE(BITAND(D.PROPERTY
, 3)
, 2
, 'REF'
, 'HARD') FROM SYS."_CURRENT_EDITION_OBJ" O
, SYS.DISK_AND_FIXED_OBJECTS PO
, SYS.DEPENDENCY$ D
, SYS.USER$ U
, SYS.USER$ PU WHERE O.OBJ# = D.D_OBJ#
AND O.OWNER# = U.USER#
AND PO.OBJ# = D.P_OBJ#
AND PO.OWNER# = PU.USER# AND ( O.OWNER# IN (USERENV('SCHEMAID')
, 1 /* PUBLIC */) OR ( ( ( (O.TYPE# = 7 OR O.TYPE# = 8 OR O.TYPE# = 9 OR O.TYPE# = 28 OR O.TYPE# = 29 OR O.TYPE# = 56) AND O.OBJ# IN (SELECT OBJ#
FROM SYS.OBJAUTH$
WHERE GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO)
AND PRIVILEGE# = 12 /* EXECUTE */) ) OR ( O.TYPE# = 4 AND O.OBJ# IN (SELECT OBJ#
FROM SYS.OBJAUTH$
WHERE GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO)
AND PRIVILEGE# IN (3 /* DELETE */
, 6 /* INSERT */
, 7 /* LOCK */
, 9 /* SELECT */
, 10 /* UPDATE */
, 17 /* READ */)) ) OR EXISTS ( SELECT NULL
FROM SYS.SYSAUTH$
WHERE GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO) AND ( ( /* PROCEDURE */ (O.TYPE# = 7 OR O.TYPE# = 8 OR O.TYPE# = 9 OR O.TYPE# = 28 OR O.TYPE# = 29 OR O.TYPE# = 56) AND ( PRIVILEGE# = -144 /* EXECUTE ANY PROCEDURE */ OR PRIVILEGE# = -141 /* CREATE ANY PROCEDURE */ ) ) OR ( /* TRIGGER */ O.TYPE# = 12 AND PRIVILEGE# = -152 /* CREATE ANY TRIGGER */ ) OR ( /* PACKAGE BODY */ O.TYPE# = 11 AND PRIVILEGE# = -141 /* CREATE ANY PROCEDURE */ ) OR ( /* VIEW */ O.TYPE# = 4 AND ( PRIVILEGE# IN ( -91 /* CREATE ANY VIEW */
, -45 /* LOCK ANY TABLE */
, -47 /* SELECT ANY TABLE */
, -397/* READ ANY TABLE */
, -48 /* INSERT ANY TABLE */
, -49 /* UPDATE ANY TABLE */
, -50 /* DELETE ANY TABLE */) ) ) OR ( /* TYPE */ O.TYPE# = 13 AND ( PRIVILEGE# = -184 /* EXECUTE ANY TYPE */ OR PRIVILEGE# = -181 /* CREATE ANY TYPE */ ) ) OR ( /* TYPE BODY */ O.TYPE# = 14 AND PRIVILEGE# = -181 /* CREATE ANY TYPE */ ) ) ) ) ) /* DON'T WORRY ABOUT TABLES
, SEQUENCES
, SYNONYMS SINCE THEY CANNOT */ /* DEPEND ON ANYTHING */ )