How to purge sysaux tablespace

Selcen Sahin August 9, 2018 ORACLE

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:

SELECT  occupant_name "Item", space_usage_kbytes/1048576 "Space Used (GB)",schema_name "Schema", move_procedure "Move Procedure" FROM v$sysaux_occupants ORDER BY 2 desc;

 Item        Space Used (GB)     Schema

SM/AWR    127,532836914063      SYS

SM/AWR    127,532836914063      SYS

SM/AWR    127,532836914063      SYS

SM/AWR    127,532836914063      SYS

SM/ADVISOR 0,606689453125    SYS

SELECT occupant_name "Item", space_usage_kbytes/1048576 "Space Used (GB)",schema_name "Schema", move_procedure "Move Procedure" FROM v$sysaux_occupants ORDER BY 2 desc;

Item Space Used (GB) Schema

SM/AWR 127,532836914063 SYS

SM/ADVISOR 0,606689453125 SYS

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

select * from (select owner,segment_name||'~'||partition_name segment_name,bytes/(1024*1024) size_m  from dba_segments  where tablespace_name = 'SYSAUX' ORDER BY BLOCKS desc) where rownum < 11;

SYS WRH$_EVENT_HISTOGRAM_PK~WRH$_EVENT__2410857060_95950 21612,625

SYS WRH$_EVENT_HISTOGRAM~WRH$_EVENT__2410857060_95950 14004,4375

SYS WRH$_ACTIVE_SESSION_HISTORY~WRH$_ACTIVE_2410857060_95950 12026,625

SYS WRH$_LATCH_MISSES_SUMMARY_PK~WRH$_LATCH__2410857060_95950 8841

SYS WRH$_LATCH_MISSES_SUMMARY~WRH$_LATCH__2410857060_95950 6281,25

SYS WRH$_SYSSTAT_PK~WRH$_SYSSTA_2410857060_95950 5651,1875

SYS WRH$_LATCH~WRH$_LATCH_2410857060_95950 5576,5625

SYS WRH$_SQLSTAT~WRH$_SQLSTA_2410857060_95950 5140,6875

SYS WRH$_LATCH_PK~WRH$_LATCH_2410857060_95950 4932

SYS WRH$_SYSSTAT~WRH$_SYSSTA_2410857060_95950 3560

select * from (select owner,segment_name||'~'||partition_name segment_name,bytes/(1024*1024) size_m from dba_segments where tablespace_name = 'SYSAUX' ORDER BY BLOCKS desc) where rownum < 11;

SYS WRH$_EVENT_HISTOGRAM_PK~WRH$_EVENT__2410857060_95950 21612,625

SYS WRH$_EVENT_HISTOGRAM~WRH$_EVENT__2410857060_95950 14004,4375

SYS WRH$_ACTIVE_SESSION_HISTORY~WRH$_ACTIVE_2410857060_95950 12026,625

SYS WRH$_LATCH_MISSES_SUMMARY_PK~WRH$_LATCH__2410857060_95950 8841

SYS WRH$_LATCH_MISSES_SUMMARY~WRH$_LATCH__2410857060_95950 6281,25

SYS WRH$_SYSSTAT_PK~WRH$_SYSSTA_2410857060_95950 5651,1875

SYS WRH$_LATCH~WRH$_LATCH_2410857060_95950 5576,5625

SYS WRH$_SQLSTAT~WRH$_SQLSTA_2410857060_95950 5140,6875

SYS WRH$_LATCH_PK~WRH$_LATCH_2410857060_95950 4932

SYS WRH$_SYSSTAT~WRH$_SYSSTA_2410857060_95950 3560

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:

select min(snap_id),MAX(snap_id) from dba_hist_snapshot;

139799    142735

select min(snap_id),MAX(snap_id) from dba_hist_snapshot;

139799 142735

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:

SELECT owner,  segment_name,  partition_name,  segment_type,  bytes/1024/1024/1024 Size_GB FROM dba_segments WHERE segment_name='WRH$_EVENT_HISTOGRAM';

1	SELECT owner, segment_name, partition_name, segment_type, bytes/1024/1024/1024 Size_GB FROM dba_segments WHERE segment_name='WRH$_EVENT_HISTOGRAM';

Here we can see WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2410857060_95950 partition didn’t dropped although the snapshot data in it has expired.

select min(snap_id), max(snap_id) from sys.WRH$_EVENT_HISTOGRAM partition (WRH$_EVENT__2410857060_95950)

95950 141582

select min(snap_id), max(snap_id) from sys.WRH$_EVENT_HISTOGRAM partition (WRH$_EVENT__2410857060_95950)

95950 141582

If the data in partition didn’t expire but the partition grows manually, splitting the partition is a solution:

alter session set "_swrf_test_action"=72;

1	alter session set "_swrf_test_action"=72;

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

exec dbms_workload_repository.drop_snapshot_range(95950,141582,2410857060);

1	exec dbms_workload_repository.drop_snapshot_range(95950,141582,2410857060);

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

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux