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

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

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

Short descTo get the tree list of the objects on which an object depends
OsAny
Tested on 
RemarksThe 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 byMichel Cadot
DateFeb 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
/


SELECT NAME, REFERENCED_NAME
FROM USER_DEPENDENCIES
WHERE TYPE = 'VIEW'
AND NAME = <NAME OF THE VIEW>;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值