SYSAUX tablespace grows because of statistics retention
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_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
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;
It is also possible to purge the statistics history, through the use of the same package.
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.