[20230516]完善spext.sql脚本.txt

--//以前写抽取sql profile outline的脚本有一些小问题,加入sqlobj$auxdata 表,增加显示ad.description,ad.last_modified
--//格式化如下:

column hint format a100
column name format a40
column description format a40
column last_modified format a30
SELECT EXTRACTVALUE (VALUE (h), '.') AS hint
      ,so.name
      ,substr(ad.description,1,40) description
      ,ad.last_modified
FROM SYS.sqlobj$data od
      ,SYS.sqlobj$ so
      ,SYS.sqlobj$auxdata ad
      ,TABLE
       (
          XMLSEQUENCE
          (
             EXTRACT (XMLTYPE (od.comp_data), '/outline_data/hint')
          )
       ) h
WHERE     (   so.NAME IN ('profile &&1', 'tuning &&1', 'switch tuning &&1')
            OR LOWER (so.name) LIKE LOWER ('%&&1%')
            OR LOWER (ad.description) LIKE LOWER('%&&1%') )
       AND so.signature = od.signature
       AND so.CATEGORY = od.CATEGORY
       AND so.obj_type = od.obj_type
       AND so.plan_id = od.plan_id
       AND so.signature = ad.signature
       AND so.category = ad.category;

ITPub博客

[20230516]完善spext.sql脚本.txt

最新文章