博客 / 詳情
返回[20260121]關於display_raw函數.txt
--//在查看錶統計信息的字段最大以及最小值，經常會使用定義的函數display_raw，在tpt的desc11.sql腳本前面有一段該函數的定義以
--//及説明。
--//在生產系統我使用的腳本許多情況並沒有使用該函數，主要原因我們許多系統是11g，許多情況下生產系統不允許在sys下建立函數，
--//或許以前沒有注意這些細節問題。
-- descxx11.sql is for Oracle 11.x and lower. Use descxx.sql if you are on Oracle 12.1 or newer
--
-- On versions older than 12c you need to create a PL/SQL stored procedure:
-- descxx.sql requires the display_raw function which is included in the comment section below.
-- the display_raw function is taken from Greg Rahn's blog as I'm too lazy to write one myself
-- http://structureddata.org/2007/10/16/how-to-display-high_valuelow_value-columns-from-user_tab_col_statistics/
--
-- create or replace function display_raw (rawval raw, type varchar2)
--return varchar2
--is
-- cn number;
-- cv varchar2(128);
-- cd date;
-- cnv nvarchar2(128);
-- cr rowid;
-- cc char(128)
--begin
-- if (type = 'NUMBER') then
-- dbms_stats.convert_raw_value(rawval, cn);
-- return to_char(cn);
-- elsif (type = 'VARCHAR2') then
-- dbms_stats.convert_raw_value(rawval, cv);
-- return to_char(cv);
-- elsif (type = 'DATE') then
-- dbms_stats.convert_raw_value(rawval, cd);
-- return to_char(cd);
-- elsif (type = 'NVARCHAR2') then
-- dbms_stats.convert_raw_value(rawval, cnv);
-- return to_char(cnv);
-- elsif (type = 'ROWID') then
-- dbms_stats.convert_raw_value(rawval, cr);
-- return to_char(cnv);
-- elsif (type = 'CHAR') then
-- dbms_stats.convert_raw_value(rawval, cc);
-- return to_char(cc);
-- else
-- return 'UNKNOWN DATATYPE';
-- end if;
--end;
--/
--
-- grant execute on display_raw to public;
-- create public synonym display_raw for display_raw;
--//今天檢查發現rowid以及NVARCHAR2類型並不存在dbms_stats.convert_raw_value函數。通過測試説明問題。
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.
SYS@book> @ descv sys.dbms_stats "-A 3 convert_raw_value"
PROCEDURE ALTER_DATABASE_TAB_MONITORING
Argument Name Type In/Out Default?
------------------------------ ------------- ------ --------
PROCEDURE CONVERT_RAW_VALUE
Argument Name Type In/Out Default?
RAWVAL RAW IN
RESVAL VARCHAR2 OUT
PROCEDURE CONVERT_RAW_VALUE
Argument Name Type In/Out Default?
RAWVAL RAW IN
RESVAL DATE OUT
PROCEDURE CONVERT_RAW_VALUE
Argument Name Type In/Out Default?
RAWVAL RAW IN
RESVAL NUMBER OUT
PROCEDURE CONVERT_RAW_VALUE
Argument Name Type In/Out Default?
RAWVAL RAW IN
RESVAL BINARY_FLOAT OUT
PROCEDURE CONVERT_RAW_VALUE
Argument Name Type In/Out Default?
RAWVAL RAW IN
RESVAL BINARY_DOUBLE OUT
PROCEDURE CONVERT_RAW_VALUE_NVARCHAR
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Argument Name Type In/Out Default?
RAWVAL RAW IN
RESVAL NVARCHAR2 OUT
PROCEDURE CONVERT_RAW_VALUE_ROWID
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Argument Name Type In/Out Default?
RAWVAL RAW IN
RESVAL ROWID OUT
--//注意看下劃線支持nvarchar2，rowid轉換的類型的函數是CONVERT_RAW_VALUE_NVARCHAR，CONVERT_RAW_VALUE_ROWID。
--//仔細看轉換不支持timestamp類型。
2.使用原來的函數測試看看。
COTT@book01p> create table t2 ( id number ,vc nvarchar2(32),vrowid rowid);
Table created.
--//表字段包含rowid類型，那裏會使用不是很清楚。
SCOTT@book01p> insert into t2 (id ,vc ) select rownum,dbms_random.string('a',16) from dual connect by level <1e3;
999 rows created.
SCOTT@book01p> update t2 set vrowid=rowid ;
999 rows updated.
SCOTT@book01p> commit ;
Commit complete.
--//分析表略。
SCOTT@book01p> @ desc t2
Name Null? Type
------- ----- -------------
1 ID NUMBER
2 VC NVARCHAR2(32)
3 VROWID ROWID
SCOTT@book01p> @ desc12c t2 ''
eXtended describe of t2
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 SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value
----- ---------- ----------- ------------------- ---- ----------- ----- ------------- ------------ -------------- ---------- --------- ----------- ---------------------------------------- ----------------------------------------
SCOTT T2 999 2026-01-21 10:45:38 1 ID NUMBER(,) 999 .00100100100 0 1 1 999
999 2026-01-21 10:45:38 2 VC NVARCHAR2(64) 999 .00100100100 0 1 A B U F j O D o h r a q w A f c z t K f P s K g P Q K z B Y t o
999 2026-01-21 10:45:38 3 VROWID ROWID(10) 999 .00100100100 0 1 訊C 訊N
--//很明顯vc，vrowid的轉換出了問題,vc的顯示字段包括空格。
--//注:desc12c.sql直接在sql語句的開頭使用函數，另外發現1個小問題，查詢的NVARCHAR2(64)，估計存在byte，字符單位不同導致的.
--//如果插入漢字到vc字段可以看出明顯的問題。
SCOTT@book01p> insert into t2 (id ,vc ) values (1e3 ,'文化');
1 row created.
SCOTT@book01p> commit ;
Commit complete.
--//分析表。
COTT@book01p> @ desc12c t2 ''
eXtended describe of t2
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 SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value
----- ---------- ----------- ------------------- ---- ----------- ----- ------------- ------------ -------------- --------- --------- ----------- ---------------------------------------- ---------------
SCOTT T2 1000 2026-01-21 11:05:20 1 ID NUMBER(,) 1000 .00100000000 0 1 1 1000
1000 2026-01-21 11:05:20 2 VC NVARCHAR2(64) 1000 .00100000000 0 1 A B U F j O D o h r a q w A f c e嘢
999 2026-01-21 11:05:20 3 VROWID ROWID(10) 999 .00100100100 1 1 訊C 訊N
--//vc的最大值根本不對,採用descxx.sql腳本：
SCOTT@book01p> @ tpt/descxx t2
Col# Column Name Null? Type # distinct Density # nulls Histogram # buckets Low Value High Value
---- ----------- ---------- ------------- ---------- -------------- ------- --------- --------- -------------------------------- ------------
1 ID NUMBER(,) 1000 .00100000000 0 HYBRID 254 1 1000
2 VC NVARCHAR2(64) 1000 .00100000000 0 HYBRID 254 A B U F j O D o h r a q w A f c e嘢
3 VROWID ROWID(10) 999 .00100100000 1 HYBRID 254 訊C 訊N
--//很明顯vc，vrowid的轉換出了問題,vc的顯示字段包括空格,最大值也是亂碼。
--//修改採用函數是CONVERT_RAW_VALUE_NVARCHAR，CONVERT_RAW_VALUE_ROWID後。
--//修改desc12c.sql腳本後測試。
SCOTT@book01p> @ desc12c t2 ''
eXtended describe of t2
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 SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value
----- ---------- ----------- ------------------- ---- ----------- ----- ------------- ------------ -------------- --------- --------- ----------- ------------------ ------------------
SCOTT T2 1000 2026-01-21 11:05:20 1 ID NUMBER(,) 1000 .00100000000 0 1 1 1000
1000 2026-01-21 11:05:20 2 VC NVARCHAR2(64) 1000 .00100000000 0 1 ABUFjODohraqwAfc 文化
999 2026-01-21 11:05:20 3 VROWID ROWID(10) 999 .00100100100 1 1 AAAtONAAMAAAAhDAAA AAAtONAAMAAAAhOACH
--//更正後顯示正常!!
--//使用我自己寫的desczz.sql腳本，裏面使用UTL_RAW.cast_to_XXXX函數。
SCOTT@book01p> @ descv sys.UTL_RAW "-A 5 cast_to_"
FUNCTION BIT_AND RETURNS RAW
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
FUNCTION CAST_TO_BINARY_DOUBLE RETURNS BINARY_DOUBLE
Argument Name Type In/Out Default?
R RAW IN
ENDIANESS BINARY_INTEGER IN DEFAULT
FUNCTION CAST_TO_BINARY_FLOAT RETURNS BINARY_FLOAT
Argument Name Type In/Out Default?
R RAW IN
ENDIANESS BINARY_INTEGER IN DEFAULT
FUNCTION CAST_TO_BINARY_INTEGER RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
R RAW IN
ENDIANESS BINARY_INTEGER IN DEFAULT
FUNCTION CAST_TO_NUMBER RETURNS NUMBER
Argument Name Type In/Out Default?
R RAW IN
FUNCTION CAST_TO_NVARCHAR2 RETURNS NVARCHAR2
Argument Name Type In/Out Default?
R RAW IN
FUNCTION CAST_TO_RAW RETURNS RAW
Argument Name Type In/Out Default?
C VARCHAR2 IN
FUNCTION CAST_TO_VARCHAR2 RETURNS VARCHAR2
Argument Name Type In/Out Default?
R RAW IN
FUNCTION COMPARE RETURNS NUMBER
Argument Name Type In/Out Default?
R1 RAW IN
--//該函數沒有date,timestamp,rowid的轉換。
COTT@book01p> @ desczz t2 ''
eXtended describe of t2
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 SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value
----- ---------- ----------- ------------------- ---- ----------- ----- ------------- ------------ ------------ --------- --------- ------------ ---------------- -----------
SCOTT T2 1000 2026-01-21 11:05:20 1 ID NUMBER(,) 1000 .00100000000 0 1 1 1000
1000 2026-01-21 11:05:20 2 VC NVARCHAR2(64) 1000 .00100000000 0 1 ABUFjODohraqwAfc 文化
999 2026-01-21 11:05:20 3 VROWID ROWID(10) 999 .00100100100 1 1
--//注：我寫的腳本不支持rowid轉換。我一直使用我自己寫的desczz.sql腳本，該問題一直很難發現或者忽略了。
--//説明該函數的正確寫法如下：
/* Formatted on 2026-01-21 11:01:45 (QP5 v5.277) */
CREATE OR REPLACE FUNCTION display_raw (rawval RAW, TYPE VARCHAR2)
RETURN VARCHAR2
IS
cn NUMBER;
CV VARCHAR2 (128);
cd DATE;
cnv NVARCHAR2 (128);
cr ROWID;
cc CHAR (128);
cbf BINARY_FLOAT;
cbd BINARY_DOUBLE;
BEGIN
IF (TYPE = 'VARCHAR2')
THEN
DBMS_STATS.convert_raw_value (rawval, CV);
RETURN TO_CHAR (CV);
ELSIF (TYPE = 'DATE')
THEN
DBMS_STATS.convert_raw_value (rawval, cd);
RETURN TO_CHAR (cd);
ELSIF (TYPE = 'NUMBER')
THEN
DBMS_STATS.convert_raw_value (rawval, cn);
RETURN TO_CHAR (cn);
ELSIF (TYPE = 'BINARY_FLOAT')
THEN
DBMS_STATS.convert_raw_value (rawval, cbf);
RETURN TO_CHAR (cbf);
ELSIF (TYPE = 'BINARY_DOUBLE')
THEN
DBMS_STATS.convert_raw_value (rawval, cbd);
RETURN TO_CHAR (cbd);
ELSIF (TYPE = 'NVARCHAR2')
THEN
DBMS_STATS.convert_raw_value_nvarchar (rawval, cnv);
RETURN TO_CHAR (cnv);
ELSIF (TYPE = 'ROWID')
THEN
DBMS_STATS.convert_raw_value_rowid (rawval, cr);
RETURN TO_CHAR (cr);
ELSIF (TYPE = 'CHAR')
THEN
DBMS_STATS.convert_raw_value (rawval, cc);
RETURN TO_CHAR (cc);
ELSE
RETURN 'UNKNOWN DATATYPE';
END IF;
END;
/
3.小結:
--//任何問題都不要想當然，許多情況直接拿別人的腳本，在使用前還是給仔細閲讀，特別對於生產系統使用，再次強調測試再測試,
--//不要忽視一些小的細節。
4.附上測試使用的腳本代碼：
$ cat descv.sql
set linesize 80
set term off
def _desc_tmpfile=&_tpt_tempdir/desc_&_tpt_tempfile..tmp
spool &_desc_tmpfile
desc &1
spool off
set term on
set linesize 269
--host sed -n '1,/----------/p;/&2/Ip' tmpdesc.txt
--host sed -n '1,/----------/p' &_desc_tmpfile ; egrep -i "&2" &_desc_tmpfile
host sed -n '0,/----------/p' &_desc_tmpfile ; sed -n '/----------/,$p' &_desc_tmpfile | grep -v -- "----------"| egrep -i &2
--host &_DELETE tmpdesc.txt
host &_DELETE &_desc_tmpfile
--//注：_tpt_tempdir,_&_tpt_tempfile在tpt腳本包的init.sql初始化定義。另外參數2最好要使用雙引號，特殊情況下還要加入單引號
--//，我以前也考慮寫成egrep -i "&2",這樣無法再追加參數,比如像前面的-A 3參數，但是如果查詢包括|字符就存在問題，例子如下：
SCOTT@book01p> @ descv t2 id|vc
Name Null? Type
------------------------ ----- --------------
/bin/bash: vc: command not found
grep: write error: Broken pipe
SCOTT@book01p> @ descv t2 "id|vc"
Name Null? Type
------------------------ ----- --------------
/bin/bash: vc: command not found
grep: write error: Broken pipe
SCOTT@book01p> @ descv t2 "'id|vc'"
Name Null? Type
------------------------ ----- --------------
1 ID NUMBER
2 VC NVARCHAR2(32)
3 VROWID ROWID
SCOTT@book01p> @ descv t2 '"id|rowid"'
Name Null? Type
------------------------ ----- --------------
1 ID NUMBER
3 VROWID ROWID
--//加入單雙引號組合就可以查詢，不知道有什麼好方法解決該問題。
$ cat desc12c.sql
/* Formatted on 2026-01-20 09:31:46 (QP5 v5.277) */
-- 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 A20
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
WITH FUNCTION display_raw (rawval RAW, TYPE VARCHAR2)
RETURN VARCHAR2
IS
CV VARCHAR2 (128);
cd DATE;
cn NUMBER;
cbf BINARY_FLOAT;
cbd BINARY_DOUBLE;
cnv NVARCHAR2 (128);
cr ROWID;
cc CHAR (128);
BEGIN
IF (TYPE = 'VARCHAR2')
THEN
DBMS_STATS.convert_raw_value (rawval, CV);
RETURN TO_CHAR (CV);
ELSIF (TYPE = 'DATE')
THEN
DBMS_STATS.convert_raw_value (rawval, cd);
RETURN TO_CHAR (cd);
ELSIF (TYPE = 'NUMBER')
THEN
DBMS_STATS.convert_raw_value (rawval, cn);
RETURN TO_CHAR (cn);
ELSIF (TYPE = 'BINARY_FLOAT')
THEN
DBMS_STATS.convert_raw_value (rawval, cbf);
RETURN TO_CHAR (cbf);
ELSIF (TYPE = 'BINARY_DOUBLE')
THEN
DBMS_STATS.convert_raw_value (rawval, cbd);
RETURN TO_CHAR (cbd);
ELSIF (TYPE = 'NVARCHAR2')
THEN
DBMS_STATS.convert_raw_value_nvarchar (rawval, cnv);
RETURN TO_CHAR (cnv);
ELSIF (TYPE = 'ROWID')
THEN
DBMS_STATS.convert_raw_value_rowid (rawval, cr);
RETURN TO_CHAR (cr);
ELSIF (TYPE = 'CHAR')
THEN
DBMS_STATS.convert_raw_value (rawval, cc);
RETURN TO_CHAR (cc);
ELSE
RETURN 'UNKNOWN DATATYPE';
END IF;
END;
SELECT owner desc_owner
,table_name desc_table_name
,sample_size
,last_analyzed
,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,
,num_distinct
,density desc_density
,num_nulls
,CASE WHEN histogram = 'NONE' THEN NULL ELSE histogram END histogram
,num_buckets
,display_raw (low_value, data_type) trans_low
,display_raw (high_value, data_type) trans_high
FROM dba_tab_cols
WHERE UPPER (table_name) LIKE
UPPER (CASE WHEN INSTR ('&&1', '.') > 0 THEN SUBSTR ('&&1', INSTR ('&&1', '.') + 1) ELSE '&&1' END)
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
/
