oracle – 用于查找包的多级依赖关系的脚本

我有一个包引用了同一模式和其他模式中的许多对象.我想找到包的所有依赖项.我只能从user_dependencies获得第一级依赖项.另外utldtree会给我依赖于我当前对象的对象.但是,orttree也只给出了同一模式中引用的对象.

当我试图在网上找到解决方案时,我遇到了以下链接
 http://rodgersnotes.wordpress.com/2012/01/05/notes-on-deptree/
在他提到的地方,他使用自己的脚本来查找对象的多级依赖关系.

你能帮我解决一下,如何继续这样一个脚本,它会让我们得到一个对象的多层依赖关系(例如,如果包引用了视图,那么我们的脚本应该提到视图和表/视图当我们进入deptree时,我们的观点是建立的)

对于大多数情况,您可以在user_dependencies上使用connect by.

确定依赖关系

适用于任何Oracle用户的示例,因为PUBLIC已被授予对user_dependencies的select访问权限：

select name,type,prior name,prior type
from   user_dependencies
start 
with   name='BUBS#MUNT_EENHEDEN'
and    type='PACKAGE'
connect 
by     nocycle 
       name = prior referenced_name
and    type = prior referenced_type

样本输出

Level 1: BUBS#MUNT_EENHEDEN   PACKAGE
Level 2: BUBS_MUNT_EENHEDEN_V VIEW    BUBS#MUNT_EENHEDEN    PACKAGE
Level 3: BUBS#VERTALINGEN     PACKAGE BUBS_MUNT_EENHEDEN_V  VIEW
Level 4: ITGEN_LANGUAGES_V    VIEW    BUBS#VERTALINGEN      PACKAGE

复杂的场景

对于复杂的场景,我发现有必要直接在数据字典上使用自己的视图.只有当您知道自己在做什么以及想要支持哪种RDBMS版本时,才能执行此操作！例如,datamodel版本引入了数据字典中的主要更改.

样品：

create or replace force view itgen_object_tree_changes_r
as 
select o_master.obj#        ojt#,o_master.name        ojt_name,o.mtime              ojt_ref_mtime,o.name               ojt_ref_name,o.owner#             ojt_ref_owner#,decode
       ( o.type#,'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',40,'LOB PARTITION',41,'LOB SUBPARTITION',42,nvl
       ( ( select 'REWRITE EQUIVALENCE'
       from    sys.sum$s
       where   s.obj# = o.obj#
       and     bitand ( s.xpflags,8388608 ) = 8388608 ),'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,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'WINDOW GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP',82,'MINING MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE FOLDER',95,'CUBE BUILD PROCESS','UNDEFINED'
       )
       ojt_ref_type
from   sys.obj$o,( /* All dependencies from the object if there are any. */
                  select distinct connect_by_root d_obj# obj#,dep.p_obj# obj_ref#
         from   sys.dependency$dep
         connect
         by     nocycle dep.d_obj# = prior dep.p_obj#
         start
         with   dep.d_obj# in ( select obj.obj# from itgen_schemas_r sma,sys.obj$obj where obj.owner# = sma.owner# )
         union all /* Union all allowed,'in' ignores duplicates. */
         /* The object itself. */
         select obj.obj#,obj.obj#
         from   itgen_schemas_r sma,sys.obj$obj
         where  obj.owner# = sma.owner#
       ) deps,sys.obj$o_master
where  o_master.obj# = deps.obj#
and    o.obj# = deps.obj_ref#
--
-- View: itgen_object_tree_changes_r
--
-- Overview of dependencies between a master object and all objects used by it. It can be used to analyze the reason why a project version views must be recalculated.
--
-- Code (alias): ote_r
--
-- Category: Hardcoded.
--
-- Example:
--
-- The object 'X' is invalid,since 'Y' is invalid.
--

oracle – 用于查找包的多级依赖关系的脚本

猜你在找的Oracle相关文章