To get the tree list of the objects on which an object depends

本文提供了一个SQL脚本，用于查询特定数据库对象所依赖的所有其他对象的层级列表。此脚本适用于Oracle数据库，要求调用者具备相应的权限以访问系统底层表。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Short desc	To get the tree list of the objects on which an object depends
Os	Any
Tested on
Remarks	The calling user must have the select privilege on the SYS underlying tables. For instance, DBA role, or SELECT ANY TABLE privilege in 8i and SELECT ANY DICTIONARY in 9i.
Submitted by	Michel Cadot
Date	Feb 2004
Category
Description	This script gives the tree of all objects on which the passing object depends.

Define THEOWNER = &1
Define THEOBJECT = &2
Define THETYPE = &3
Set linesize 100
Column obj_ format a100 heading "'&THEOWNER..&THEOBJECT' (&THETYPE) depends on"
select substr(lpad(' ',2*(r.lvl-1))||
     decode(o.linkname, null, u.name, o.remoteowner)||
     '.'||o.name||
     decode(o.linkname, null, '', '@'||o.linkname)||
     ' ('||decode(o.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',
          13, 'TYPE', 14, 'TYPE BODY',
          19, 'TABLE PARTITION',
          20, 'INDEX PARTITION', 21, 'LOB',
          22, 'LIBRARY', 23, 'DIRECTORY',
          24, 'QUEUE', 28, 'JAVA SOURCE',
          29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
          32, 'INDEXTYPE', 33, 'OPERATOR',
          34, 'TABLE SUBPARTITION',
          35, 'INDEX SUBPARTITION',
          39, 'LOB PARTITION',
          40, 'LOB SUBPARTITION',
          42, 'MATERIALIZED VIEW',
          43, 'DIMENSION', 44, 'CONTEXT',
          46, 'RULE SET',
          47, 'RESOURCE PLAN', 48, 'CONSUMER GROUP',
          51, 'SUBSCRIPTION', 52, 'LOCATION',
          55, 'XML SCHEMA',
          56, 'JAVA DATA', 57, 'SECURITY PROFILE',
          62, 'EVALUATION CONTEXT',
          'UNDEFINED')||')'
     ,1,100) obj_
from sys.disk_and_fixed_objects o, sys.user$ u,
     ( select level lvl, d.p_obj# obj#, rownum nb
       from sys.dependency$ d
       connect by d.d_obj# = prior d.p_obj#
       start with d.d_obj# in
          ( select obj# from sys.obj$
            where owner# = (select user# from sys.user$
          where name = upper('&THEOWNER'))
     and name = upper('&THEOBJECT')
     and type# = decode(upper('&THETYPE'), 'NEXT OBJECT', 0,
          'INDEX', 1, 'TABLE', 2, 'CLUSTER', 3,
          'VIEW', 4, 'SYNONYM', 5,
          'SEQUENCE', 6, 'PROCEDURE', 7,
          'FUNCTION', 8, 'PACKAGE', 9,
          'PACKAGE BODY', 11, 'TRIGGER', 12,
          'TYPE', 13, 'TYPE BODY', 14,
          'TABLE PARTITION', 19,
          'INDEX PARTITION', 20, 'LOB', 21,
          'LIBRARY', 22, 'DIRECTORY', 23,
          'QUEUE', 24, 'JAVA SOURCE', 28,
          'JAVA CLASS', 29, 'JAVA RESOURCE', 30,
          'INDEXTYPE', 32, 'OPERATOR', 33,
          'TABLE SUBPARTITION', 34,
          'INDEX SUBPARTITION', 35,
          'LOB PARTITION', 39,
          'LOB SUBPARTITION', 40,
          'MATERIALIZED VIEW', 42,
          'DIMENSION', 43, 'CONTEXT', 44,
          'RULE SET', 46, 'RESOURCE PLAN', 47,
          'CONSUMER GROUP', 48,
          'SUBSCRIPTION', 51, 'LOCATION', 52,
          'XML SCHEMA', 55, 'JAVA DATA', 56,
          'SECURITY PROFILE', 57,
          'EVALUATION CONTEXT', 62)
     ) ) r
where u.user# = o.owner#
  and o.obj# = r.obj#
order by r.nb
/