ORACLE如何找出视图依赖的对象和视图嵌套层数

之前写过一篇文章“SQL Server如何找出视图依赖的对象和视图嵌套层数”，这里我介绍一下Oracle数据库中如何找出视图的依赖对象以及视图嵌套层数关系。主要通过DBA_DEPENDENCIES这个系统视图（这个系统视图中包含有对象的依赖关系数据）。另外，我们使用了Oracle的树形查询（层级查询）来展示这种层级关系。对比SQL Server数据库与Oracle数据库的SQL来说，感觉Oracle由于拥有非常给力的系统函数，感觉写出来的SQL更优雅与简洁。如果你对代码简洁优雅有股执着与偏执的话。就会有这样的感觉。

1. --==================================================================================================================
   
2. --        ScriptName            :            get_view_referenced_objects.sql
   
3. --        Author                :            潇湘隐者    
   
4. --        CreateDate            :            2018-08-03
   
5. --        Description           :            查看视图引用的对象
   
6. --        Note                  :             
   
7. /*-*****************************************************************************************************************
   
8.         Parameters              :                                    参数说明
   
9. ********************************************************************************************************************
   
10.             &OWNER              :            视图的OWNER
    
11.             &VIEW_NAME          :            视图的名称
    
12. ********************************************************************************************************************
    
13.    Modified Date    Modified User     Version                 Modified Reason
    
14. ********************************************************************************************************************
    
15.     2018-08-03        潇湘隐者         V01.00.00        新建该脚本。
    
16. *******************************************************************************************************************/
    
17. SELECT  V.ROW_LEVEL
    
18.        ,V.OBJECT_OWNER
    
19.        ,V.OBJECT_NAME
    
20.        ,V.OBJECT_TYPE
    
21.        ,V.REFERENCED_OWNER
    
22.        ,V.REFERENCED_NAME
    
23.        ,O.OBJECT_TYPE  AS REFERENCED_OBJECT_TYPE
    
24. FROM
    
25. (
    
26. SELECT LEVEL                AS ROW_LEVEL
    
27.       ,D.OWNER              AS OBJECT_OWNER
    
28.       ,D.NAME               AS OBJECT_NAME
    
29.       ,D.TYPE               AS OBJECT_TYPE
    
30.       ,D.REFERENCED_OWNER   AS REFERENCED_OWNER
    
31.       ,D.REFERENCED_NAME    AS REFERENCED_NAME
    
32. FROM DBA_DEPENDENCIES D 
    
33. START WITH D.OWNER=UPPER('&OWNER') AND D.NAME =UPPER('&VIEW_NAME') AND D.TYPE='VIEW'
    
34. CONNECT BY NOCYCLE PRIOR D.REFERENCED_OWNER = D.OWNER
    
35.                AND PRIOR  D.REFERENCED_NAME =D.NAME
    
36. ) V
    
37. INNER JOIN DBA_OBJECTS O ON V.REFERENCED_OWNER =O.OWNER AND V.REFERENCED_NAME=O.OBJECT_NAME
    
38. ORDER BY V.ROW_LEVEL,V.OBJECT_OWNER,V.OBJECT_NAME;

复制代码

这个脚本虽然展示了视图依赖对象的关系，但是感觉还是不够直观，我想将视图依赖的对象用>>这种链条关系给直观的展示出来，所以有了下面脚本。

1. --==================================================================================================================
   
2. --        ScriptName            :            get_view_referenced_objects.sql
   
3. --        Author                :            潇湘隐者    
   
4. --        CreateDate            :            2021-06-15
   
5. --        Description           :            查看视图引用的对象
   
6. --        Note                  :            此脚本get_view_referenced_objects.sql的第二个版本。
   
7. /*-*****************************************************************************************************************
   
8.         Parameters              :                                    参数说明
   
9. ********************************************************************************************************************
   
10.             &OWNER              :            视图的OWNER
    
11.             &VIEW_NAME          :            视图的名称
    
12. ********************************************************************************************************************
    
13.    Modified Date    Modified User     Version                 Modified Reason
    
14. ********************************************************************************************************************
    
15.     2018-08-03        潇湘隐者         V01.00.00        新建该脚本。
    
16. *******************************************************************************************************************/
    
17. 
    
18. SELECT LEVEL                AS ROW_LEVEL
    
19.       ,D.OWNER              AS OBJECT_OWNER
    
20.       ,D.NAME               AS OBJECT_NAME
    
21.       ,D.TYPE               AS OBJECT_TYPE
    
22.       ,PRIOR(D.OWNER ||'.' || D.NAME) 
    
23.                             AS PARNET_OBJECT_NAME
    
24.       ,sys_connect_by_path(D.OWNER ||'.' ||D.NAME,'>>') 
    
25.        || '>>' || D.REFERENCED_OWNER || '.' ||  D.REFERENCED_NAME AS NESTED_VIEW_PATH
    
26.       ,D.REFERENCED_OWNER   AS REFERENCED_OWNER
    
27.       ,D.REFERENCED_NAME    AS REFERENCED_NAME
    
28. FROM DBA_DEPENDENCIES D 
    
29. START WITH D.OWNER=UPPER('&OWNER') AND D.NAME =UPPER('&VIEW_NAME') AND D.TYPE='VIEW'
    
30. CONNECT BY NOCYCLE PRIOR D.REFERENCED_OWNER = D.OWNER
    
31.                AND PRIOR D.REFERENCED_NAME =D.NAME
    
32. ORDER BY ROW_LEVEL, OBJECT_OWNER, OBJECT_NAME;

复制代码

其实我写这个SQL的目的是将数据库中嵌套超过1层的视图给找出来，嵌套层数过多的视图对SQL性能来说往往是一个灾难，而且是仅仅灾难的开始，而且嵌套视图也是SQL性能优化中一个很头疼的问题。如果你能杜绝这种现象，最好将其扼杀在萌芽状态，如果你无法杜绝的话，性能优化中，你会经常与其打交道。那么问题来了，一个数据库里面如果存在视图嵌套视图或者说嵌套超过2层的视图，我们如何将其找出来呢？ 这里分析一个我写的脚本，简单测试过了，应该没有什么问题，如有问题，欢迎反馈指教。
注意：这个SQL只是找出视图的嵌套关系，如果要找出嵌套2层或超过2层的视图，加上一个查询条件即可。这里不做展开赘述了

1. --==================================================================================================================
   
2. --        ScriptName            :            get_netsted_view_level.sql
   
3. --        Author                :            潇湘隐者    
   
4. --        CreateDate            :            2023-06-01
   
5. --        Description           :            查看/找出数据库视图嵌套视图信息(例如嵌套层数/嵌套层次关系)
   
6. --        Note                  :            这里使用了一个中间表T_OBJECT_DEPENDENCIES存储数据，主要原因是因为直接查询DBA_DEPENDENCIES
   
7. --                                           的SQL性能非常差.
   
8. /*-*****************************************************************************************************************
   
9.         Parameters              :                                    参数说明
   
10. ********************************************************************************************************************
    
11.                                 :            无参数
    
12. ********************************************************************************************************************
    
13.    Modified Date    Modified User     Version                 Modified Reason
    
14. ********************************************************************************************************************
    
15.     2023-06-01        潇湘隐者         V01.00.00        新建该脚本。
    
16. *******************************************************************************************************************/
    
17. DROP TABLE T_OBJECT_DEPENDENCIES PURGE;
    
18. CREATE TABLE T_OBJECT_DEPENDENCIES
    
19. AS
    
20. SELECT * FROM DBA_DEPENDENCIES 
    
21. WHERE OWNER NOT IN ('SYS','SYSTEM', 'OLAPSYS', 'PUBLIC', 'CTXSYS', 'DVSYS','APEX_040200', 'AUDSYS'
    
22.                     ,'WMSYS','XDB', 'LBACSYS','LBACSYS', 'MDSYS', 'IC_ADMIN','GSMADMIN_INTERNAL', 'DBSNMP'
    
23.                    );
    
24. 
    
25. 
    
26. WITH NESTED_VIEW  AS 
    
27. (
    
28. SELECT LEVEL                AS ROW_LEVEL
    
29.       ,D.OWNER              AS OBJECT_OWNER
    
30.       ,D.NAME               AS OBJECT_NAME
    
31.       ,D.TYPE               AS OBJECT_TYPE
    
32.       ,PRIOR(D.OWNER ||'.' || D.NAME) 
    
33.                             AS PARNET_OBJECT_NAME
    
34.       ,sys_connect_by_path(D.OWNER ||'.' ||D.NAME,'>') AS NestViewPath
    
35.       ,D.REFERENCED_OWNER   AS REFERENCED_OWNER
    
36.       ,D.REFERENCED_NAME    AS REFERENCED_NAME
    
37. FROM T_OBJECT_DEPENDENCIES D 
    
38. START WITH  D.TYPE='VIEW' 
    
39. CONNECT BY NOCYCLE PRIOR D.REFERENCED_OWNER =D.OWNER
    
40.                AND PRIOR D.REFERENCED_NAME =D.NAME
    
41. )
    
42. SELECT DISTINCT SUBSTR(NestViewPath, 2, DECODE(INSTR(NestViewPath, '>',1,2), 0,  LENGTH(NestViewPath)-1, INSTR(NestViewPath, '>',1,2)-2)) AS PARENT_OBJ_NAME,
    
43.        NestViewPath ||'>' ||REFERENCED_OWNER ||'.' || REFERENCED_NAME AS NestViewPath,
    
44.        REFERENCED_NAME, ROW_LEVEL   
    
45. FROM  NESTED_VIEW
    
46. ORDER BY 1;
    
47. DROP TABLE T_OBJECT_DEPENDENCIES PURGE;

复制代码

扫描上面二维码关注我如果你真心觉得文章写得不错，而且对你有所帮助，那就不妨帮忙“推荐"一下，您的“推荐”和”打赏“将是我最大的写作动力！本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接.
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！