[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