What to do when SYSAUX Tablespace Grows Rapidly

 There is no single source for higher than expected growth in the SYSAUX tablespace:

 There are however a few common sources that can be investigated and confirmed quickly using a few views and scripts

One important view is V$SYSAUX_OCCUPANTS

Another very useful and provided script is the AWRINFO.SQL script found in the Oracle_home/rdbms/admin directory

Let's start by taking a closer look at V$SYSAUX_OCCUPANTS*

   select OCCUPANT_NAME,OCCUPANT_DESC

      from V$SYSAUX_OCCUPANTS

      order by SPACE_USAGE_KBYTES desc

  

select SPACE_USAGE_KBYTES, OCCUPANT_NAME

      from V$SYSAUX_OCCUPANTS

      order by  1 desc

SPACE_USAGE_KBYTES OCCUPANT_NAME

------------------ --------------------------------

(1)            118144 SM/AWR

(2)             23360 SM/OPTSTAT

                13696 LOGMNR

(3)             10368 SM/ADVISOR ***

                 6720 SM/OTHER

                 3584 WM

                 3264 SMON_SCN_TIME   

 

 

1. Check how many partitions do exist for the offending table

select table_name,partition_name from dba_tab_partitions

where table_name = 'WRH$_ACTIVE_SESSION_HISTORY';

2. Try to force the creation of multiple partitions by splitting any current oversized partitions:

alter session set "_swrf_test_action" = 72;

3. Now that we have more partitions and less data per partition for a more unique period of time

  Drop snapshots manually using a small[er] range.

DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(

low_snap_id IN NUMBER,

high_snap_id IN NUMBER

dbid IN NUMBER DEFAULT NULL);

 - or -

exec dbms_stats.purge_stats(sysdate-&days);

4. If the drop snapshot did not finish or consume for UNDO space. Terminate the session and wait for a day or two for the default auto purge 

the job should automatically drop the old snapshots

For reference Oracle Metalink Doc: 1292724.1,329984.1