Display table column statistics

Recently came across a script(STAT_SANITY.sql) on the RoughSea website, which can be used to display table column statistics.

I created an alternative script, which can be used for the same purpose:

rem script: tab_col_stats.sql
rem    check table column statistics collected by dbms_stats
rem

rem --- create a function to be used in the sql

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  
  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;
/

rem part 2

set linesize 120
col table_name format a15
col column_name format a20
col low_val format a15
col high_val format a15
select
        a.column_name,
        a.num_distinct distincts,
        a.num_nulls nulls,
        a.density,
 a.num_buckets bkts,
 display_raw_(a.low_value,b.data_type) as low_val,   
 display_raw_(a.high_value,b.data_type) as high_val
from
        dba_tab_col_statistics a,
 dba_tab_cols    b
where   a.table_name = upper('&tabname')
and     a.owner=upper('&owner')
and     a.table_name = b.table_name
and     a.column_name = b.column_name
and     a.owner = b.owner
order by 1
/

rem drop the function if necessary

drop function display_raw_
/

Sample output:

COLUMN_NAME           DISTINCTS      NULLS    DENSITY       BKTS LOW_VAL         HIGH_VAL
-------------------- ---------- ---------- ---------- ---------- --------------- ---------------
ACCOUNT_ID              9101384          0 1.0987E-07          1 0               632471375
ARCHIVE_DATE                  0   51654447          0          1
INT_MASTER_ORDER_ID       86208          0   .0000116          1 0               127140720
INT_ORDER_ID           51654447          0 1.9359E-08          1 7               127141566
INT_PQ_ORDER_ID          199175          0 5.0207E-06          1 0               127141560
INT_SUPP_ORDER_ID       5361989          0 5.1838E-07         21 0               334768529
IS_MASTER                     2          0         .5          1 N               Y
IS_PQ                         2          0         .5          1 N               Y
IS_VALID                      2          0         .5          1 N               Y
ORDER_COMP_DATE        13043687    8614054 7.6665E-08          1 2004-03-07 10:0 2009-04-03 20:0
                                                                 1:20            0:02

ORDER_ID               51456267          0 1.9434E-08          1 CADX102866094   RSSI093199412
ORDER_SAVE_DATE        15392369        260 6.4967E-08          1 2004-03-20 02:5 2009-04-03 20:0
                                                                 5:17            0:01