pl/sql相关的视图

最新推荐文章于 2024-09-14 08:00:00 发布

aoerqileng

最新推荐文章于 2024-09-14 08:00:00 发布

阅读量749

点赞数

CC 4.0 BY-SA版权

分类专栏： plsql

本文链接：https://blog.youkuaiyun.com/aoerqileng/article/details/50547271

plsql 专栏收录该内容

28 篇文章

订阅专栏

本文介绍如何利用PL/SQL的视图，如user_trigger_cols和user_identifiers来跟踪程序中的标识符使用。通过user_identifiers视图，可以监控标识符的使用，但需启用PL/SQL SCOPE功能。同时，文章还讨论了检测未引用的异常声明。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

select * from user_arguments;
select * from user_dependencies;
select * from user_errors;
select * from user_identifiers;
select * from user_object_size;
select * from USER_PLSQL_OBJECT_SETTINGS ;
select * from USER_PROCEDURES;
select * from USER_SOURCE where name='F';
select * from USER_STORED_SETTINGS;
select * from user_triggers;

select * from user_trigger_cols;

对于select * from user_identifiers;这个视图来说，可以跟踪程序中的标识符的使用，要使用该功能，需要启用pl/scope

ALTER SESSION SET 
plscope_settings='IDENTIFIERS:ALL'
/

plscore_settings的值有2个， IDENTIFIERS:ALL or IDENTIFIERS:NONE，默认是NONE

下面是检测哪些异常被声明了但是从未被引用

WITH subprograms_with_exception
        AS (SELECT DISTINCT owner
                          , object_name
                          , object_type
                          , name
              FROM all_identifiers has_exc
             WHERE     has_exc.owner = USER
                   AND has_exc.usage = 'DECLARATION'
                   AND has_exc.TYPE = 'EXCEPTION'),
     subprograms_with_raise_handle
        AS (SELECT DISTINCT owner
                          , object_name
                          , object_type
                          , name
              FROM all_identifiers with_rh
             WHERE     with_rh.owner = USER
                   AND with_rh.usage = 'REFERENCE'
                   AND with_rh.TYPE = 'EXCEPTION')
SELECT *
  FROM subprograms_with_exception
MINUS
SELECT *
  FROM subprograms_with_raise_handle