昨晚，看了一些统计信息的内容，由于里面的信息是RAW datatypes.看了别人写的代码，转换对应的数值：
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(32);
cd date;
cnv nvarchar2(32);
cr rowid;
cc char(32);
begin
type=substr(type,1,9)
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;
/
select
a.column_name,
display_raw(a.low_value,b.data_type) as low_val,
display_raw(a.high_value,b.data_type) as high_val,
b.data_type
from
dba_tab_col_statistics a, dba_tab_cols b
where
a.owner = b.owner
a.table_name='FOO' and
a.table_name=b.table_name and
a.column_name=b.column_name
/
http://mwidlake.wordpress.com/2010/02/24/update-to-decoding-high-and-low-values/
-- col_stats
-- Martin Widlake mdw 21/03/2003
-- MDW 11/12/09 enhanced to include more translations of low_value/high_value
-- pilfered from Gary Myers blog
-- MDW 20/02/10 added in the handling of timestamps.
col owner form. a6 word wrap
col table_name form. a15 word wrap
col column_name form. a22 word wrap
col data_type form. a12
col M form. a1
col num_vals form. 99999,999
col dnsty form. 0.9999
col num_nulls form. 99999,999
col low_v form. a30
col low_v2 form. a18
col hi_v form. a30
col data_type form. a10
col low_value form. a25
col high_value form. a25
set lines 110
break on owner nodup on table_name nodup
spool col_stats.lst
select --owner
-- ,table_name
column_name
,data_type
,decode (nullable,'N','Y','N') M
,num_distinct num_vals
,num_nulls
,density dnsty
,decode(substr(data_type,1,9) -- as there are several timestamp types
,'NUMBER' ,to_char(utl_raw.cast_to_number(low_value))
,'VARCHAR2' ,to_char(utl_raw.cast_to_varchar2(low_value))
,'NVARCHAR2' ,to_char(utl_raw.cast_to_nvarchar2(low_value))
,'BINARY_DO',to_char(utl_raw.cast_to_binary_double(low_value))
,'BINARY_FL' ,to_char(utl_raw.cast_to_binary_float(low_value))
,'DATE',rtrim(
to_char(100*(to_number(substr(low_value,1,2),'XX')-100)
+ (to_number(substr(low_value,3,2),'XX')-100),'fm0000')||'-'||
to_char(to_number(substr(low_value,5,2),'XX'),'fm00')||'-'||
to_char(to_number(substr(low_value,7,2),'XX'),'fm00')||' '||
to_char(to_number(substr(low_value,9,2),'XX')-1,'fm00')||':'||
to_char(to_number(substr(low_value,11,2),'XX')-1,'fm00')||':'||
to_char(to_number(substr(low_value,13,2),'XX')-1,'fm00'))
,'TIMESTAMP',rtrim(
to_char(100*(to_number(substr(low_value,1,2),'XX')-100)
+ (to_number(substr(low_value,3,2),'XX')-100),'fm0000')||'-'||
to_char(to_number(substr(low_value,5,2),'XX'),'fm00')||'-'||
to_char(to_number(substr(low_value,7,2),'XX'),'fm00')||' '||
to_char(to_number(substr(low_value,9,2),'XX')-1,'fm00')||':'||
to_char(to_number(substr(low_value,11,2),'XX')-1,'fm00')||':'||
to_char(to_number(substr(low_value,13,2),'XX')-1,'fm00')
||'.'||to_number(substr(low_value,15,8),'XXXXXXXX') )
) low_v
,decode(substr(data_type,1,9) -- as there are several timestamp types
,'NUMBER' ,to_char(utl_raw.cast_to_number(high_value))
,'VARCHAR2' ,to_char(utl_raw.cast_to_varchar2(high_value))
,'NVARCHAR2' ,to_char(utl_raw.cast_to_nvarchar2(high_value))
,'BINARY_DO',to_char(utl_raw.cast_to_binary_double(high_value))
,'BINARY_FL' ,to_char(utl_raw.cast_to_binary_float(high_value))
,'DATE',rtrim(
to_char(100*(to_number(substr(high_value,1,2),'XX')-100)
+ (to_number(substr(high_value,3,2),'XX')-100),'fm0000')||'-'||
to_char(to_number(substr(high_value,5,2),'XX'),'fm00')||'-'||
to_char(to_number(substr(high_value,7,2),'XX'),'fm00')||' '||
to_char(to_number(substr(high_value,9,2),'XX')-1,'fm00')||':'||
to_char(to_number(substr(high_value,11,2),'XX')-1,'fm00')||':'||
to_char(to_number(substr(high_value,13,2),'XX')-1,'fm00'))
,'TIMESTAMP',rtrim(
to_char(100*(to_number(substr(high_value,1,2),'XX')-100)
+ (to_number(substr(high_value,3,2),'XX')-100),'fm0000')||'-'||
to_char(to_number(substr(high_value,5,2),'XX'),'fm00')||'-'||
to_char(to_number(substr(high_value,7,2),'XX'),'fm00')||' '||
to_char(to_number(substr(high_value,9,2),'XX')-1,'fm00')||':'||
to_char(to_number(substr(high_value,11,2),'XX')-1,'fm00')||':'||
to_char(to_number(substr(high_value,13,2),'XX')-1,'fm00')
||'.'||to_char(to_number(substr(high_value,15,8),'XXXXXXXX')))
, high_value
) hi_v
,low_value,high_value
from dba_tab_columns
where owner like upper('&tab_own')
and table_name like upper(nvl('&tab_name','WHOOPS')||'%')
ORDER BY owner,table_name,COLUMN_ID
/
clear colu
spool off
clear breaks
第1个链接不支持许多类型比如timestamp，自己参照第1个例子做了一些修改：
第2个链接还原timestamp最大值时的尾数部分写错，应该是原作者to_char(to_number(substr(high_value,15,8),'XXXXXXXX')）。
utl_raw.cast_to_binary_double(rawval)返回的是负数，前面加一个负号才正确！还有如果double，float为负数的话，转化还存在问题，我不知道现在问题在那里？不过这种类型的数据在实践中使用很少。
create or replace function display_raw (rawval raw, data_type varchar2)
return varchar2
is
cn number;
cv varchar2(32);
cd date;
ct timestamp(6);
-- cnv nvarchar2(32);
cr rowid;
cc char(32);
db BINARY_DOUBLE;
type1 varchar2(32);
begin
type1 := substr(data_type,1,9);
if (type1 = 'NUMBER') then
dbms_stats.convert_raw_value(rawval, cn);
return to_char(cn);
elsif (type1 = 'VARCHAR2') then
dbms_stats.convert_raw_value(rawval, cv);
return to_char(cv);
elsif (type1 = 'DATE') then
dbms_stats.convert_raw_value(rawval, cd);
return to_char(cd);
elsif (type1 = 'TIMESTAMP') then
dbms_stats.convert_raw_value(rawval, cd);
return to_char(cd)||'.'||to_char(to_number(substr(rawval,15,8),'XXXXXXXX'));
elsif (type1 = 'NVARCHAR2') then
return to_char(utl_raw.cast_to_nvarchar2(rawval));
--dbms_stats.convert_raw_value(rawval, cnv);
--return to_char(cnv);
elsif (type1 = 'ROWID') then
dbms_stats.convert_raw_value(rawval, cr);
return to_char(cr);
elsif (type1 = 'CHAR') then
dbms_stats.convert_raw_value(rawval, cc);
return to_char(cc);
elsif (type1 = 'BINARY_DO') then
return to_char(-utl_raw.cast_to_binary_double(rawval));
--dbms_stats.convert_raw_value(rawval, db);
--return to_char(db);
elsif (type1 = 'BINARY_FL') then
return to_char(-utl_raw.cast_to_binary_float(rawval));
else
return 'UNKNOWN DATATYPE';
end if;
end;
/