递归列出 Oracle DBA_DEPENDENCIES视图的集中度

Question

我想要一个给定视图的依赖表(最终)列表。

例如:

SELECT NAME, TYPE, REFERENCED_NAME, REFERENCED_TYPE
FROM DBA_DEPENDENCIES
WHERE OWNER='FOO'
AND NAME='VIEW_O1'

结果:

VIEW_O1 VIEW    TABLE_01    TABLE
VIEW_O1 VIEW    TABLE_02    TABLE
VIEW_O1 VIEW    TABLE_03    TABLE
VIEW_O1 VIEW    VIEW_02     VIEW
VIEW_O1 VIEW    VIEW_03     VIEW

我希望它像:

VIEW_O1 VIEW    TABLE_01    TABLE
VIEW_O1 VIEW    TABLE_02    TABLE
VIEW_O1 VIEW    TABLE_03    TABLE
VIEW_O1 VIEW    VIEW_02     VIEW
VIEW_O1 VIEW    VIEW_03     VIEW
VIEW_O2 VIEW    TABLE_03    TABLE
VIEW_O2 VIEW    TABLE_04    TABLE
VIEW_O3 VIEW    TABLE_05    TABLE
VIEW_O3 VIEW    VIEW_04     VIEW
VIEW_O4 VIEW    TABLE_06    TABLE
VIEW_O4 VIEW    TABLE_07    TABLE
VIEW_O4 VIEW    TABLE_08    TABLE

我想我还应该有一列列出起始点，这样我就可以将祖先保存在一个组中。

我尝试了以下查询:

SELECT NAME, TYPE, REFERENCED_NAME, REFERENCED_TYPE
FROM DBA_DEPENDENCIES
WHERE OWNER='FOO'
AND NAME='VIEW_01'
CONNECT BY PRIOR REFERENCED_NAME=NAME

，但我得到一个错误，读取'ORA-01436: CONNECT BY循环在用户数据'。我错过了什么?

Answer 1

要在CONNECT BY后面指定NOCYCLE关键字:

。

SELECT NAME, 
       TYPE, 
       REFERENCED_NAME, 
       REFERENCED_TYPE 
  FROM DBA_DEPENDENCIES 
 WHERE OWNER='FOO' 
   AND NAME='VIEW_01' 
CONNECT BY NOCYCLE
  PRIOR REFERENCED_NAME = NAME;

这里有更多关于NOCYCLE和"CONNECT_BY_ISCYCLE"关键字的信息:http://www.dba-oracle.com/t_advanced_sql_connect_by_loop.htm

这里:http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns001.htm

希望对你有帮助。

编辑:在注释之后，您错过了START WITH子句。

SELECT NAME, 
       TYPE, 
       REFERENCED_NAME, 
       REFERENCED_TYPE 
  FROM DBA_DEPENDENCIES 
 WHERE OWNER='FOO' 
 START WITH NAME='VIEW_01' 
CONNECT BY NOCYCLE
  PRIOR REFERENCED_NAME = NAME;

顺便说一句，保留OWNER='FOO' where子句限制了任何返回到FOO对象的依赖关系，所以你可能会错过其他模式的依赖关系。

编辑2:表视图的主键是owner, name，因此选择应该以两者开始，并以两者连接。你可以使用where来过滤出想要的结果。

SELECT OWNER, NAME, TYPE,  
   REFERENCED_OWNER,
   REFERENCED_NAME, 
   REFERENCED_TYPE 
FROM DBA_DEPENDENCIES 
-- where referenced_type='TABLE'
START WITH owner = 'FOO' AND NAME='VIEW_01' 
CONNECT BY NOCYCLE
   PRIOR REFERENCED_NAME = NAME
   AND PRIOR REFERENCED_OWNER = OWNER;

Answer 2

正如Ollie所说，这就像:
这个查询解析所有以开头的深度MYPROC作为树的根。

SELECT A.NAME,
   A.TYPE,
   A.REFERENCED_OWNER,
   A.REFERENCED_NAME,
   A.REFERENCED_TYPE,
   A.REFERENCED_LINK_NAME,
   A.SCHEMAID  FROM USER_DEPENDENCIES A
 CONNECT BY PRIOR REFERENCED_NAME = A.NAME
 START WITH A.NAME = 'MYPROC'ORDER BY 1, 2, 3, 4