SQL> create or replace function display_raw (rawval raw, type varchar2)
2 return varchar2
3 is
4 cn number;
5 cv varchar2(32);
6 cd date;
7 cnv nvarchar2(32);
8 cr rowid;
9 cc char(32);
10 begin
11 if (type = 'NUMBER') then
12 dbms_stats.convert_raw_value(rawval, cn);
13 return to_char(cn);
14 elsif (type = 'VARCHAR2') then
15 dbms_stats.convert_raw_value(rawval, cv);
16 return to_char(cv);
17 elsif (type = 'DATE') then
18 dbms_stats.convert_raw_value(rawval, cd);
19 return to_char(cd);
20 elsif (type = 'NVARCHAR2') then
21 dbms_stats.convert_raw_value(rawval, cnv);
22 return to_char(cnv);
23 elsif (type = 'ROWID') then
24 dbms_stats.convert_raw_value(rawval, cr);
25 return to_char(cnv);
26 elsif (type = 'CHAR') then
27 dbms_stats.convert_raw_value(rawval, cc);
28 return to_char(cc);
29 else
30 return 'UNKNOWN DATATYPE';
31 end if;
32 end;
33 /
Function created
SQL>
SQL> select
2 a.column_name, a.partition_name,
3 a.num_distinct,
4 display_raw(a.low_value,b.data_type) as low_val,
5 display_raw(a.high_value,b.data_type) as high_val,
6 b.data_type
7 from
8 dba_part_col_statistics a, dba_tab_cols b
9 where
10 a.owner='SH' and
11 a.table_name='SALES' and
12 a.table_name=b.table_name and
13 a.column_name=b.column_name and
14 a.column_name = 'TIME_ID' and
15 a.low_value is not null
16 order by 1, 2
17 /
COLUMN_NAME PARTITION_NAME NUM_DISTINCT LOW_VAL HIGH_VAL DATA_TYPE
-------------------------------------------------------------------------------- ------------------------------ ------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
TIME_ID SALES_Q1_1998 90 01-JAN-98 31-MAR-98 DATE
TIME_ID SALES_Q1_1999 90 01-JAN-99 31-MAR-99 DATE
TIME_ID SALES_Q1_2000 91 01-JAN-00 31-MAR-00 DATE
TIME_ID SALES_Q1_2001 90 01-JAN-01 31-MAR-01 DATE
TIME_ID SALES_Q2_1998 91 01-APR-98 30-JUN-98 DATE
TIME_ID SALES_Q2_1999 91 01-APR-99 30-JUN-99 DATE
TIME_ID SALES_Q2_2000 91 01-APR-00 30-JUN-00 DATE
TIME_ID SALES_Q2_2001 90 01-APR-01 30-JUN-01 DATE
TIME_ID SALES_Q3_1998 92 01-JUL-98 30-SEP-98 DATE
TIME_ID SALES_Q3_1999 92 01-JUL-99 30-SEP-99 DATE
TIME_ID SALES_Q3_2000 92 01-JUL-00 30-SEP-00 DATE
TIME_ID SALES_Q3_2001 92 01-JUL-01 30-SEP-01 DATE
TIME_ID SALES_Q4_1998 92 01-OCT-98 31-DEC-98 DATE
TIME_ID SALES_Q4_1999 92 01-OCT-99 31-DEC-99 DATE
TIME_ID SALES_Q4_2000 92 01-OCT-00 31-DEC-00 DATE
TIME_ID SALES_Q4_2001 92 01-OCT-01 31-DEC-01 DATE
16 rows selected