递归列出Oracle的DBA_Dependency视图的内容

2022-03-04 00:00:00 oracle oracle10g recursive-query

我希望(最终)给定视图的从属表列表。

例如:

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:通过用户数据中的循环连接’。我错过了什么?


解决方案

您要在连接后指定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;

btw,保留Owner=‘foo’WHERE子句会将任何依赖项限制为仅返回到foo的对象,因此您可能会错过来自其他架构的依赖项。

编辑2: 视图表的主键是OWNER,NAME,因此SELECT应该以两者开头,并通过两者连接。您可以使用"在哪里过滤发布所需结果"。

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;

相关文章