I have an Oracle 11g R2 (11.2.0.1) database where I noticed the SYSAUX tablespace was growing larger every day. Searched Oracle My Support and found Doc ID 1292724.1 and Doc ID 552880.1 which were helpful.
After running awrinfo.sql, I found the largest consumer to be SM/OPTSTAT at 2.8 GB which is larger and not typical when compared to my other Oracle 10g R2 and Oracle 11g R1 databases.
******************************************************** (1b) SYSAUX occupants space usage (v$sysaux_occupants) ******************************************************** | | Occupant Name Schema Name Space Usage | -------------------- -------------------- ---------------- | SM/OPTSTAT SYS 2,726.8 MB | SM/AWR SYS 76.6 MB | SM/ADVISOR SYS 8.2 MB | LOGMNR SYSTEM 7.9 MB | SM/OTHER SYS 6.8 MB | JOB_SCHEDULER SYS 3.6 MB ********************************** (4) Space usage by non-AWR components (> 500K) ********************************** COMPONENT MB SEGMENT_NAME SEGMENT_TYPE --------- --------- --------------------------------------- -------------- NON_AWR 800.0 SYS.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX NON_AWR 752.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY TABLE NON_AWR 424.0 SYS.I_WRI$_OPTSTAT_H_ST INDEX NON_AWR 240.0 SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY TABLE NON_AWR 192.0 SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX NON_AWR 104.0 SYS.I_WRI$_OPTSTAT_HH_ST INDEX
Searching Google, I came upon John Hallas’ “Purging statistics from the SYSAUX tablespace” blog entry quite informative and detailed on managing occupants in the SYSAUX tablespace.
As John mentions:
“Whenever statistics in the dictionary are modified, old versions of statistics are saved automatically for future restoring. The old statistics are purged automatically at regular intervals based on the statistics history retention setting and the time of recent statistics gathering performed in the system. Retention is configurable using the ALTER_STATS_HISTORY_RETENTION procedure. The default value is 31 days.”
To help reduce the amount of space caused by historical data, I set the retention of old statistics to 14 days.
exec dbms_stats.alter_stats_history_retention(14);
To verify the new setting, I executed the statement below.
select dbms_stats.get_stats_history_retention from dual;
I proceeded to incrementally purge statistics from 31 days to 14 days. Started with 28 days, then moved to 21 days.
exec dbms_stats.purge_stats(sysdate-28);
Then I tried 14 days, however I encountered an UNDO space error requiring me to change my increments using 18, 16, and finally 14 to complete the purge task.
Now to reclaim the space used by the objects shown in section 4 of the AWRINFO report, I will eventually follow John Hallas’ steps using the ALTER TABLE MOVE command.
Posted by mrothouse 
