递归列出Oracle DBA_DEPENDENCIES视图的集中 [英] Recursively list concents of Oracle's DBA_DEPENDENCIES view

问题描述

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

I would like a list of dependent tables (ultimately) of a given view.

例如:

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

我想我也应该有一列列出起点的列，这样我就可以将祖先归为一类.

I suppose that I should also have a column that lists the starting point, so I can keep the ancestry in a group.

我尝试了以下查询:

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循环".我想念什么?

but I get an error that reads 'ORA-01436: CONNECT BY loop in user data'. What am I missing?

推荐答案

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

You want to specify the NOCYCLE keyword after your CONNECT BY:

即

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

There is more info on NOCYCLE and the "CONNECT_BY_ISCYCLE" keywords here: 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子句.

After comments, you have missed the START WITH clause.

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对象的所有依赖项限制为可能，因此您可能会错过其他模式的依赖项.

BTW, keeping the OWNER='FOO' where clause limits any dependencies returned to just FOO's object so you may possibly miss dependencies from other schemas.

视图表的主键是所有者，即名称，因此，选择应以两者开头并通过两者进行连接.您可以在何处过滤出所需的结果.

Edit 2: The primary key of a table of view is owner, name thus the select should start with both and connect by both. You can use where to filter out desired results.

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;

这篇关于递归列出Oracle DBA_DEPENDENCIES视图的集中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！