I've noticed that on some of my databases, the SYSAUX tablespace kept on growing to unreasonable proportions.

The following objects would grow to several gigabytes in size .
   
SYS    WRI$_OPTSTAT_HISTGRM_HISTORY    TABLE
SYS    WRI$_OPTSTAT_HISTHEAD_HISTORY    TABLE
SYS    WRI$_OPTSTAT_IND_HISTORY    TABLE
SYS    I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST    INDEX
SYS    I_WRI$_OPTSTAT_H_ST        INDEX


This is due to the retention of system statistics by Oracle (default 1 month).


SQL> select dbms_stats.get_stats_history_retention present_retention from dual;

PRESENT_RETENTION
-----------------
               31


It is possible to modify the retention period through the dbms_stats package.

SQL> exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(7);

PL/SQL procedure successfully completed.


SQL>  select dbms_stats.get_stats_history_retention present_retention from dual;

PRESENT_RETENTION
-----------------
                7

It is also possible to purge the statistics history, through the use of the same package.


exec DBMS_STATS.PURGE_STATS(SYSDATE-7);


Beware, the process can be quite long (several hours), and undo tablespace-consuming.
Be sure you dispose of enough diskspace to accomodate the resulting rollback segment and archivelog creation, and be prepared to delete archivelogs through RMAN.

Leave me a note if this post has been useful to you.

Happy computing

Nixman.