[20260117]建立完善descvv.sql脚本.txt

[20260117]建立完善descvv.sql脚本.txt --//前一段时间写了一个descv.sql脚本，使用正则表达式过滤一些字段，我开始主要想法过滤一些系统视图的情况。 SYS@book> @ descv v$sql "is_|sql_id"            Name                            Null?    Type            ------------------------------- -------- ----------------------------     3      SQL_ID                                   VARCHAR2(13)    64      IS_OBSOLETE                              VARCHAR2(1)    65      IS_BIND_SENSITIVE                        VARCHAR2(1)    66      IS_BIND_AWARE                            VARCHAR2(1)    67      IS_SHAREABLE                             VARCHAR2(1)    91      IS_REOPTIMIZABLE                         VARCHAR2(1)    92      IS_RESOLVED_ADAPTIVE_PLAN                VARCHAR2(1)    97      IS_ROLLING_INVALID                       VARCHAR2(1)    98      IS_ROLLING_REFRESH_INVALID               VARCHAR2(1) --//我开始以为oracle没有一个视图记录视图字段的信息，实际上视图dba_tab_cols，dba_tab_columns都有记录，只不过table_name记 --//录的是V_$SQL,实际上oracle的定义V$SQL是同义词，真正的视图定义是V_$SQL. --//这样单独写一个查询脚本descvv.sql实现类似功能,直接拿tpt的脚本做一些修改。 --//实际上也可以直接使用，只不过使用v_$XXX 代替 v$xxx. @ desczz sys.v_$Sql sql_id|is_ --//输出略。 --//对比descv.sql的输出： SYS@book> @ descvv v$sql is_,sql_id eXtended describe of v$sql DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT   OWNER.TABLE_NAME  <filters> SAMPLE  : @ desczz TABLE_NAME column_name1,column_name2 IF NOT INPUT <filters> ,USE "" . Owner      Table_Name           Col# Column Name                    Null?      Type ---------- -------------------- ---- ------------------------------ ---------- -------------------- SYS        V_$SQL                  3 SQL_ID                                    VARCHAR2(13)                                   64 IS_OBSOLETE                               VARCHAR2(1)                                   65 IS_BIND_SENSITIVE                         VARCHAR2(1)                                   66 IS_BIND_AWARE                             VARCHAR2(1)                                   67 IS_SHAREABLE                              VARCHAR2(1)                                   91 IS_REOPTIMIZABLE                          VARCHAR2(1)                                   92 IS_RESOLVED_ADAPTIVE_PLAN                 VARCHAR2(1)                                   97 IS_ROLLING_INVALID                        VARCHAR2(1)                                   98 IS_ROLLING_REFRESH_INVALID                VARCHAR2(1) 9 rows selected. --//源代码如下： $ cat descvv.sql -- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions. COL desc_column_id   HEAD "Col#" FOR A4 COL desc_column_name HEAD "Column Name" FOR A30 COL desc_data_type   HEAD "Type" FOR A20 WORD_WRAP COL desc_nullable    HEAD "Null?" FOR A10 COL desc_density     HEAD "Density" FOR 9.99999999999 COL desc_owner       HEAD Owner         FOR A10 COL desc_table_name  HEAD Table_Name    FOR A20 COL trans_low        HEAD "Low_value" FOR A40 COL trans_high       HEAD "High_value" FOR A40 PROMPT eXtended describe of &1 PROMPT PROMPT DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. PROMPT INPUT   OWNER.TABLE_NAME  <filters> PROMPT SAMPLE  : @ desczz TABLE_NAME column_name1,column_name2 PROMPT IF NOT INPUT <filters> ,USE "" . PROMPT set termout off column column_string new_value column_string format a200 column 2 new_value 2 select null "2"  from dual where 1=2; select  decode('&2',null,'*','','*','1=1','*',translate('&2',',%','|*')) "2" ,decode('&2','','1=1','1','1=1','1=1','1=1','column_name in ('||''''||replace(upper('&2'),',',''',''')||''')' ) column_string from dual ; from dual; --select decode('&2','','1=1','1','1=1','1=1','1=1','column_name in ('||''''||replace(upper('&2'),',',''',''')||''')' ) column_string from dual ; --select decode('&2','','1=1','1','1=1','1=1','1=1',''''||replace(upper('&2'),',',''',''')||'''') column_string from dual ; set termout on BREAK ON desc_owner ON desc_table_name SKIP 1   SELECT owner desc_owner         ,table_name desc_table_name         ,CASE WHEN hidden_column = 'YES' THEN 'H' ELSE ' ' END || LPAD (column_id, 3) desc_column_id         ,column_name desc_column_name         ,CASE WHEN nullable = 'N' THEN 'NOT NULL' ELSE NULL END AS desc_nullable         ,   data_type          || CASE                -- WHEN data_type = 'NUMBER' THEN '('||data_precision||CASE WHEN data_scale = 0 THEN NULL ELSE ','||data_scale END||')'             WHEN data_type = 'NUMBER' THEN '(' || data_precision || ',' || data_scale || ')'                ELSE '(' || data_length || ')'             END             AS desc_data_type                                                                       --      data_default     FROM dba_tab_cols    WHERE     UPPER (table_name) LIKE                 REPLACE (                    UPPER (CASE WHEN INSTR ('&1', '.') > 0 THEN SUBSTR ('&1', INSTR ('&1', '.') + 1) ELSE '&1' END)                   ,'V$'                   ,'V_$')          AND owner LIKE                 CASE WHEN INSTR ('&1', '.') > 0 THEN UPPER (SUBSTR ('&1', 1, INSTR ('&1', '.') - 1)) ELSE USER END          --AND column_name = decode('&&2','',column_name,upper('&&2'))          --AND (&column_string)          --and column_name in (&column_string)          AND (REGEXP_LIKE (LOWER (COLUMN_NAME), LOWER ('&&2'))) ORDER BY owner, table_name, column_id / column column_string clear