Problem:
Checked alert log file and saw the following errors:
ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_3395507964_562 by 128 in tablespace SYSAUX MMON Flush encountered SYSAUX out of space error(1688). MMON (emergency) purge of WR snapshots (546) and older ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_3395507964_562 by 128 in tablespace SYSAUX MMON Flush encountered SYSAUX out of space error(1688). MMON (emergency) purge - nothing to purge, pgsid=0.
Solution:
Add a new datafile to the SYSAUX tablespace.
In SQL*Plus, add additional data file:
SQL> select * from dba_data_files where tablespace_name = 'SYSAUX'; FILE_NAME -------------------------------------------------------------------------------- FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS ---------- ------------------------------ ---------- ---------- --------- RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ------------ --- ---------- ---------- ------------ ---------- ----------- ONLINE_ ------- C:ORACLEDEV1DBAPPS_STDATASYSAUX01.DBF 35 SYSAUX 581959680 71040 AVAILABLE 35 NO 0 0 0 581894144 71032 ONLINE SQL> alter tablespace sysaux add datafile 'C:ORACLEDEV1DBAPPS_STDATASYSAUX02.DBF' size 500M; Tablespace altered. SQL> select * from dba_data_files where tablespace_name = 'SYSAUX'; FILE_NAME -------------------------------------------------------------------------------- FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS ---------- ------------------------------ ---------- ---------- --------- RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ------------ --- ---------- ---------- ------------ ---------- ----------- ONLINE_ ------- C:ORACLEDEV1DBAPPS_STDATASYSAUX01.DBF 35 SYSAUX 581959680 71040 AVAILABLE 35 NO 0 0 0 581894144 71032 ONLINE FILE_NAME -------------------------------------------------------------------------------- FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS ---------- ------------------------------ ---------- ---------- --------- RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ------------ --- ---------- ---------- ------------ ---------- ----------- ONLINE_ ------- C:ORACLEDEV1DBAPPS_STDATASYSAUX02.DBF 39 SYSAUX 524288000 64000 AVAILABLE 39 NO 0 0 0 523239424 63872 ONLINE
This is solution to the symptom. It is important to look at causes and there are many causing for SYSAUX growth. In the particular case listed above you could be facing a bug. I wrote it up here and it is worth looking so you don’t just grow you SYSAUX forever.
http://tinky2jed.wordpress.com/2013/12/05/ora-1688-unable-to-extend-table-sys-wrh_active_session_history/