递归地列出 Oracle 的 DBA_DEPENDENCIES 视图的浓度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7797922/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Recursively list concents of Oracle's DBA_DEPENDENCIES view
提问by craig
I would like a list of dependent tables (ultimately) of a given view.
我想要一个给定视图的依赖表（最终）列表。
For example:
例如：
SELECT NAME, TYPE, REFERENCED_NAME, REFERENCED_TYPE
FROM DBA_DEPENDENCIES
WHERE OWNER='FOO'
AND NAME='VIEW_O1'
The results:
结果：
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
I would like it to resemble:
我希望它类似于：
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.
我想我还应该有一列列出起点，这样我就可以将祖先保留在一个组中。
I've tried the following query:
我试过以下查询：
SELECT NAME, TYPE, REFERENCED_NAME, REFERENCED_TYPE
FROM DBA_DEPENDENCIES
WHERE OWNER='FOO'
AND NAME='VIEW_01'
CONNECT BY PRIOR REFERENCED_NAME=NAME
but I get an error that reads 'ORA-01436: CONNECT BY loop in user data'. What am I missing?
但我收到一条错误消息，内容为“ORA-01436：用户数据中的 CONNECT BY 循环”。我错过了什么？
采纳答案by Ollie
You want to specify the NOCYCLE keyword after your CONNECT BY:
您想在 CONNECT BY 之后指定 NOCYCLE 关键字：
i.e.
IE
SELECT NAME,
TYPE,
REFERENCED_NAME,
REFERENCED_TYPE
FROM DBA_DEPENDENCIES
WHERE OWNER='FOO'
AND NAME='VIEW_01'
CONNECT BY NOCYCLE
PRIOR REFERENCED_NAME = NAME;
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
这里有关于 NOCYCLE 和“CONNECT_BY_ISCYCLE”关键字的更多信息：http://www.dba-oracle.com/t_advanced_sql_connect_by_loop.htm
and here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns001.htm
在这里：http: //download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns001.htm
Hope it helps...
希望能帮助到你...
EDIT: After comments, you have missed the START WITH clause.
编辑：评论后，您错过了 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;
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.
顺便说一句，保留 OWNER='FOO' where 子句限制返回到 FOO 对象的任何依赖项，因此您可能会错过其他模式的依赖项。
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.
编辑 2：视图表的主键是所有者，名称因此选择应该以两者开头并由两者连接。您可以使用 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;
回答by R?dvan Korkmaz
as Ollie said,
This is as the same:
This query resolves all deps starting with MYPROCas root of the tree.
正如 Ollie 所说，这是一样的：
这个查询解析所有以MYPROC开始的deps作为树的根。
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
