[20260118]建立完善coldef.sql脚本.txt

[20260118]建立完善coldef.sql脚本.txt --//有时候系统视图一些字段定义varchar(1),这样写sql语句字段输出宽度，会导致显示看不全字段名。 --//通过简单例子演示说明。 1.环境： SYS@book> @ ver2 ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 21.0.0.0.0 BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. 2.演示问题： SYS@book> select sysdate from dual ; SYSDATE ------------------- 2026-01-17 11:34:02 SYS@book> @ hashz HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID ---------- ------------- ------------ ---------- ---------- ------------------- -----------  409144692 f0wzs9nc663bn            0      68980  18630d74  2026-01-17 11:34:01    16777216 SYS@book> @ col_vlist v$sql sql_id|is_ SELECT sql_id ,is_obsolete ,is_bind_sensitive ,is_bind_aware ,is_shareable ,is_reoptimizable ,is_resolved_adaptive_plan ,is_rolling_invalid ,is_rolling_refresh_invalid FROM v$sql SYS@book> 9999 where sql_id='f0wzs9nc663bn'; SYS@book> / SQL_ID        I I I I I I I I ------------- - - - - - - - - f0wzs9nc663bn N N N Y N   N N --//后面几个字段类型都是varchar(1),显示仅仅长度1，这样开头的显示字段I，根本看不出对应那个。 --//执行前必须通过col定义显示宽度，建立1个脚本实现这个功能。 SYS@book> @ coldef v$sql is_|sql_id column SQL_ID format a13 column IS_OBSOLETE format a11 column IS_BIND_SENSITIVE format a17 column IS_BIND_AWARE format a13 column IS_SHAREABLE format a12 column IS_REOPTIMIZABLE format a16 column IS_RESOLVED_ADAPTIVE_PLAN format a25 column IS_ROLLING_INVALID format a18 column IS_ROLLING_REFRESH_INVALID format a26 SYS@book> @ col_vlist v$sql sql_id|is_ SELECT sql_id ,is_obsolete ,is_bind_sensitive ,is_bind_aware ,is_shareable ,is_reoptimizable ,is_resolved_adaptive_plan ,is_rolling_invalid ,is_rolling_refresh_invalid FROM v$sql SYS@book> 9999 where sql_id='f0wzs9nc663bn' SYS@book> / SQL_ID        IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE IS_REOPTIMIZABLE IS_RESOLVED_ADAPTIVE_PLAN IS_ROLLING_INVALID IS_ROLLING_REFRESH_INVALID ------------- ----------- ----------------- ------------- ------------ ---------------- ------------------------- ------------------ -------------------------- f0wzs9nc663bn N           N                 N             Y            N                                          N                  N --//这样显示字段就很很清晰了。 3.脚本如下： $ cat  coldef.sql  $ cat coldef.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 set head off feedback off term off def _coldef_tmpfile=&_tpt_tempdir/coldef_&_tpt_tempfile..tmp spool &_coldef_tmpfile SELECT    'column '        || desc_column_name        || ' format a'        || CASE              WHEN GREATEST (column_name_length, DATA_LENGTH) = 128 THEN 30              ELSE LEAST (GREATEST (column_name_length, DATA_LENGTH), 100)           END   FROM (  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                 ,DATA_LENGTH                 ,LENGTH (column_name) column_name_length             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')))                  AND DATA_TYPE IN ('VARCHAR2', 'CHAR')         ORDER BY owner, table_name, column_id) / spool off set head on feedback 6 term on host cat &_coldef_tmpfile @  &_coldef_tmpfile host &_DELETE &_coldef_tmpfile column column_string clear