查找包的多级依赖性的脚本 [英] Script to find multi level dependencies of a package
问题描述
user_dependencies
获得一级依赖关系。另外 utldtree
会给我依赖于我当前对象的对象。 utldtree
也只给出了相同的模式。 虽然我正在网路上找到解决方案,但我遇到了以下链接
http://rodgersnotes.wordpress.com/2012/01/05/notes-on-deptree/
他提到的时候,他用自己的脚本找到一个对象的多级依赖关系。
你能帮我一下吗这样一个脚本会让我们得到一个对象的多层次的依赖关系(例如,如果程序包正在引用视图,那么我们的脚本应该提到我们看到的视图和表/视图,当我们得到 deptree
)
大多数情况下,您可以使用connect by on user_dependencies连接。
确定依赖关系
适用于任何Oracle用户的示例,因为PUBLIC已被授予对user_dependencies的选择访问权限: / p>
选择名称
pre>
,键入
,以前的名称
,先前的类型
从user_dependencies
开始
与name ='BUBS#MUNT_EENHEDEN'
和type ='PACKAGE'
连接
by nocycle
name =以前引用的名称
和type = before referenced_type
示例输出
级别1:BUBS#MUNT_EENHEDEN PACKAGE
/ pre>
级别2:BUBS_MUNT_EENHEDEN_V VIEW BUBS#MUNT_EENHEDEN PACKAGE
级别3:BUBS#VERTALINGEN包装BUBS_MUNT_EENHEDEN_V VIEW
级别4:ITGEN_LANGUAGES_V VIEW BUBS#VERTALINGEN PACKAGE
复杂场景
对于复杂场景,我发现有必要直接使用自己的视图数据字典。只有当您知道您正在做什么以及您想要支持的RDBMS版本时,才能执行此操作!例如,数据模型版本引入了数据字典的主要变化。
示例:
创建或替换强制视图itgen_object_tree_changes_r
作为
选择o_master.obj#ojt#
,o_master.name ojt_name
,o.mtime ojt_ref_mtime
,o .name ojt_ref_name
,o.owner#ojt_ref_owner#
,解码
(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'
,40,'LOB PARTITION'
,41,'LOB SUBPARTITION'
,42,nvl
((选择'REWRITE EQUIVALENCE '
from sys.sum $ s
其中s.obj#= o.obj#
和bitand(s.xpflags,8388608)= 8388608),'MATERIALIZED VIEW'
)
,43,'DIMENSION'
,44,'CONTEXT'
,46,'RULE SET'
,47,'RESOURCE PLAN'
,48, '消费者集团'
,51,'订阅'
,52,'位置'
,55,'XML SCHEMA'
,56,'JAVA DAT a'
,57,'EDITION'
,59,'RULE'
,60,'CAPTURE'
,61,'APPLY'
,62,'评估语境'
,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
,(/ *所有依赖关系来自对象如果有的话。 * /
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 #
启动
与dep.d_obj#in(select obj.obj#from itgen_schemas_r sma,sys.obj $ obj其中obj.owner#= sma.owner#)
union all / *联盟全部允许,忽略重复。 * /
/ *对象本身。 * /
select obj.obj#
,obj.obj#
from itgen_schemas_r sma
,sys.obj $ obj
其中obj.owner#= sma.owner #
)deps
,sys.obj $ o_master
其中o_master.obj#= deps.obj#
和o.obj#= deps.obj_ref#
- -
- 查看:itgen_object_tree_changes_r
-
- 主对象与其使用的所有对象之间的依赖关系概述。它可以用于分析必须重新计算项目版本视图的原因。
-
- 代码(别名):ote_r
-
- 类别:硬编码。
-
- 示例:
-
- 对象X无效,因为Y无效。
-
I've a package which references many objects from the same schema and other schemas. I want to find all the dependencies of the package. I can get only first level dependencies from
user_dependencies
. Alsoutldtree
would give me the objects which are dependent on my current object.utldtree
also gives only the referenced objects in the same schema.While I'm trying to find the solution for this on the net, I came across the following link http://rodgersnotes.wordpress.com/2012/01/05/notes-on-deptree/ where he mentioned that, he uses his own script to find the multi level dependencies of an object.
Could you please help me out, how to proceed for such a script which will get us the multi-level dependencies of an object,(for example if the package is referencing views, then our script should mention the views and the tables/views upon which our view is build as we get in
deptree
)解决方案You can use a connect by on user_dependencies for most cases.
Determining dependencies
Sample which works for any Oracle user since PUBLIC has been granted select access on user_dependencies:
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
Sample output
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
Complex scenarios
For complex scenarios I've found it necessary to use an own view directly on the data dictionary. Do this only when you know what you are doing and what RDBMS version you want to support! For instance, datamodel versions introduced major changes in the data dictionary.
Sample:
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# , 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' , 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. --
这篇关于查找包的多级依赖性的脚本的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!