dba_tab_col_statistics & dbms_stats.convert_raw_value() to display min and max value of a table in Oracle

Well, couple of days back we had a request to delete the records which are older than 6 months from audit table. The size of the table was more than 35GB. So we thought of doing this over the weekend, but still we wanted to have baseline statistics of how many rows we got to retain and how many rows we got to delete. As we analyze our tables daily we got the total number of rows in the SYS.AUD$ table easily.

SQL> select num_rows from dba_tables where table_name='AUD$';
NUM_ROWS
----------

214285904
SQL>

Thats a huge table aint'it then we wanted to check for the minimum and maximum value of the table. While google'ng around I found few articles on dbms_stats.convert_raw_value() and believe me its really helpfull to get us the minimum and maximum values of big tables in Oracle, It is used to convert the raw values to the displayable format. Since we have LOW_VALUE and HIGH_VALUE in raw type, we can easily convert it in to human readable format by using this package. We used the below block to find out what is the minimum value of column Timestamp# in Aud$ table.

 SQL> desc dba_tab_col_statistics
 Name                                      Null?    Type
 ----------------------------------------- -------- ------        OWNER                                              VARCHAR2(30)
 TABLE_NAME                                         VARCHAR2(30)
 COLUMN_NAME                                        VARCHAR2(30)
 NUM_DISTINCT                                       NUMBER
 LOW_VALUE                                          RAW(32)
 HIGH_VALUE                                         RAW(32)
 DENSITY                                            NUMBER
 NUM_NULLS                                          NUMBER
 NUM_BUCKETS                                        NUMBER
 LAST_ANALYZED                                      DATE
 SAMPLE_SIZE                                        NUMBER
 GLOBAL_STATS                                       VARCHAR2(3)
 USER_STATS                                         VARCHAR2(3)
 AVG_COL_LEN                                        NUMBER
 HISTOGRAM                                          VARCHAR2(15)

PLSQL block to convert raw values in LOW_VALUE/HIGH_VALUE columns

set serveroutput on
DECLARE
rv RAW(32) ;
dt DATE;
BEGIN
select low_VALUE into rv from dba_tab_col_statistics where TABLE_NAME='AUD$' and COLUMN_NAME='TIMESTAMP#';
dbms_stats.convert_raw_value(rv, dt);
dbms_output.put_line( TO_CHAR(dt, 'dd-MON-yyyy hh:mm'));
END;
/

PL/SQL procedure successfully completed.
01-MAY-2009 12:05
SQL>

as simple as that, else had I run "select min(timestamp#) from aud$" it would have taken atleast ~40mins to 1hour to get me the result set. We can change the tablename and column name as required,