[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 
SAMPLE  : @ desczz TABLE_NAME column_name1,column_name2
IF NOT INPUT ,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 
PROMPT SAMPLE  : @ desczz TABLE_NAME column_name1,column_name2
PROMPT IF NOT INPUT ,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