[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
--PROMPT SAMPLE : @ desczz TABLE_NAME column_name1,column_name2
--PROMPT IF NOT INPUT
--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
[20260118]建立完善coldef.sql脚本.txt
-
lfree
2026-01-18 20:47:37 -
Oracle
- 原创