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

[20260128]建立完善descvv.sql脚本.txt --//在使用自己写的descvv.sql脚本遇到的问题，通过例子说明： SCOTT@book01p> @ descvv v$sql is_ 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 "" . no rows selected --//没有任何输出。仔细检查发现参数1在执行时拆分2部分，小数点前面为owner，如果不存在小数点，等于user。 --//这样前面的执行相当于执行@ descvv scott.v$sql is_ 自然没有显示。修改如下。 SCOTT@book01p> @ descvv sys.v$sql is_ eXtended describe of sys.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                 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) 8 rows selected. --//修改完善脚本，自动判断给owner赋值sys。 --//采用简单的方法判断 case when REGEXP_LIKE (UPPER ('&1'), '^GV_\$|^GV\$|^V_\$|^V\$|^DBA_|^USER_|^CDB_') THEN 'SYS.'||'&1' END "1" SCOTT@book01p> @ descvv v$sql sql_id|child_n|is_ 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)                                   46 CHILD_NUMBER                              NUMBER(,)                                   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) 10 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 1 new_value 1 column 2 new_value 2 select null "1" ,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 ,case when REGEXP_LIKE (UPPER ('&1'), '^GV_\$|^GV\$|^V_\$|^V\$|^DBA_|^USER_|^CDB_') THEN 'SYS.'||'&1' END "1" 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 break on clear