SYSAUX tablespace 100% full

How to deal with SYSAUX  tablespace 100% full ?
The statistics used by Oracle Optimizer inside the Oracle Database Unit (DBU) are updated daily and stored for 31 days by default. For large tables, the statistics can use a significant amount of space in the SYSAUX tablespace
   
     So you have to purge the data manually.but the purge procedure will fail in most of the situation .because during the period , it will use up the UNDOTBS* space.even this, i suggest you should try it firstly. if it fail, then you can run the step 7 direclty.
     
     The following is an example that the earlist data is Sep.4 ,and i want to purge it to the current Oct.11

1. check the oldest datat in it .
         su -  oracle
         sqlplus "/ as sysdba"
 
     SQL> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
04-SEP-08 10.47.40.679300000 PM +05:30

2. purge the old stats data 


SQL>  exec dbms_stats.purge_stats(to_timestamp('20080915','YYYYMMDD'));
exec dbms_stats.purge_stats(to_timestamp('20080920','YYYYMMDD'));     
                          
exec dbms_stats.purge_stats(to_timestamp('20080925','YYYYMMDD'))                                 
exec dbms_stats.purge_stats(to_timestamp('20081001','YYYYMMDD'))                                 

exec dbms_stats.purge_stats(to_timestamp('20081005','YYYYMMDD'))                                  
exec dbms_stats.purge_stats(to_timestamp('20081010','YYYYMMDD'))                                  


3. check the old stats data has been deleted and now the latest are :

SQL> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
11-OCT-08 05.18.08.605495000 PM +05:30

4. Only after the step 1,2,3, now the old data has been pruged. even after this, the SYSAUX table space is still full.
 This is a known problem about oracle (Statistics space used by SM/OPTSTAT in the SYSAUX tablespace is not reclaimed after purging).

5. So you can run the awrinfo to check which table or index has occupied the space. then rebuild them. here for example

su - oracle
cd /u01/app/oracle/
sqlplus "/ as sysdba"
sql> @?/rdbms/admin/awrinfo

sql> ! more ./awrinfo.txt
**********************************
(4) Space usage by non-AWR components (> 500K)
**********************************

COMPONENT        MB SEGMENT_NAME                                                          SEGMENT_TYPE       
--------- --------- --------------------------------------------------------------------- ---------------    
NON_AWR       528.0 SYS.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST                                    INDEX              
NON_AWR       470.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY                                      TABLE              
NON_AWR       240.0 SYS.I_WRI$_OPTSTAT_H_ST                                               INDEX              
NON_AWR        72.0 SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY                                     TABLE              
NON_AWR        40.0 SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST                                     INDEX       

6. so we have to move the table and rebuild the index to free the SYSAUX tablespace.

ALTER TABLE SYS.WRI$_OPTSTAT_HISTGRM_HISTORY MOVE;
ALTER TABLE SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY MOVE;

alter index I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild nologging;
alter index I_WRI$_OPTSTAT_H_ST rebuild nologging;
alter index I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST rebuild nologging;

7.So one thing I have to say here is that because the old stats data are only be used to analyze the Oracle/SQL performance to give some refernce to the Oracle Adminstrator , except this, nothing is useful for this. So you can delete the infomation from the stats table direcltly)

Sql> truncate table SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
Sql> truncate table SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY ;

the INDEX is truncated automatically when truncating the table.