How to purge sysaux tablespace

Sysaux tablespace is an auxiliary tablespace to the SYSTEM tablespace. If SYSAUX tablespace becomes unusable database functionality will remain operational.

Occupants of the SYSAUX tablespace can be monitored from V$SYSAUX_OCCUPANTS view.

The largest component of the SYSAUX tablespace is the Automatic Workload Repository (AWR)

When the sysaux tablespace grows, the components are investigated:

When we look at sizes of the objects from the dba_segments that are in SYSAUX tablespace:

The biggest object is “WRH$_EVENT_HISTOGRAM~WRH$_EVENT__2410857060_95950”.

Now we can check the minimum and maximum snap_id in dba_hist_snapshot:

In large AWR tables, snapshot data is stored in partitions. One method of purging data from these tables is by removing partitions that only contain rows that have exceeded the retention criteria.

To check these partitions:

Here we can see WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2410857060_95950 partition didn’t dropped although the snapshot data in it has expired.
If the data in partition didn’t expire but the partition grows manually, splitting the partition is a solution:

After this we can start manually dropping the old partition range:

After this procedure ends, the old and big partition will be dropped.

Selcen Sahin
Author: Selcen Sahin

Leave a Reply

Your email address will not be published. Required fields are marked *