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

对于大多数情况,您可以在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#

, 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.

--