Cleaning Oracle SYSAUX Tablespace Usage

Recently in one of our Oracle instances sysaux tablespace grew huge and filled up the complete tablespace. We were also not able to resize the tablespace to create some free space.
So here in this article we will discuss on how to create some free space in SYSAUX to continue normal database operations but before going into details we shall know few basics about SYSAUX to help understand the process better.

In Oracle SYSAUX tablespace is considered as an auxiliary tablespace to the SYSTEM tablespace. This is required by Oracle as a default tablespace for many database features and products. Previous to SYSAUX oracle use to require multiple tablepsaces to support the same database features and products. Thus using the SYSAUX tablespace reduces load on SYSTEM tablespace.

Restrictions on SYSAUX tablespace

1. Using SYSAUX DATAFILE clause in the CREATE DATABASE statement you can specify only datafile attributes in SYSAUX tablespace.
2. You can not alter attributes like (PERMANENT, READ WRITE,EXTENT MANAGMENT LOCAL,SEGMENT SPACE MANAGMENT AUTO) with an ALTER TABLESPACE statement
3. SYSAUX tablespace cannot be dropped or renamed.

Now coming to our issue of oracle sysaux tablespace full, we need to do following to free up the space. To give a prospect of what was the size of SYSAUX before cleanup we have put a screenshot of SYSAUX before clean up below: ( 94% full)

SYSAUX Before Cleanup:

1. Check whats occupying SYSAUX tablespace:

Run below query to know what all occupants are there in sysaux occupying all the space.

select occupant_name,occupant_desc,space_usage_kbytes from v$sysaux_occupants

Sample Output:

Once we are sure about whats all occupying the space then we can work on it. Here in this article we have tackled with huge number of AWR reports occupying 5GB+ space(shown in below screenshot).

2. Check AWR Retention Period:

First is to check what is the AWR retention period in DB. For us it was set for 90 days which we did not require. You can query using below SQL:

select retention from dba_hist_wr_control;

We reduced it to 7 day which is 7*24*60 = 10080 minutes.  In this example the retention period is modified to 7 days (10080 minutes) and the interval between each snapshot is 60 minutes.

execute dbms_workload_repository.modify_snapshot_settings (interval => 60,retention => 10080);

In case while reducing the retention period you encounter below error then check the MOVING_WINDOW_SIZE value and update it to correct value and then execute the above AWR retention query again.

Example Error:

 

 

exec DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size( window_size =>7);

SELECT moving_window_size
FROM dba_hist_baseline
WHERE baseline_type = 'MOVING_WINDOW';

 

3. Cleanup old AWR reports to free up space:

Once the retention period is set you can follow below steps to cleanup the old AWR reports to free up space. Run below query to find the oldest and newest AWR snapshots.

SELECT
snap_id, begin_interval_time, end_interval_time
FROM
SYS.WRM$_SNAPSHOT
WHERE
snap_id = ( SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT)
UNION
SELECT
snap_id, begin_interval_time, end_interval_time
FROM
SYS.WRM$_SNAPSHOT
WHERE
snap_id = ( SELECT MAX (snap_id) FROM SYS.WRM$_SNAPSHOT)
/

Execute below command to cleanup all AWR reports between snap_id 9723 to 9920.

BEGIN
dbms_workload_repository.drop_snapshot_range(low_snap_id => 9723, high_snap_id=>9920);
END;
/

If above removal process taking too much of time then you can run below two sqls as sysdba to drop the old AWR’s and rebuild the repositories. This process is very fast.

SQL> connect / as sysdba
SQL> @?/rdbms/admin/catnoawr.sql
SQL> @?/rdbms/admin/catawrtb.sql

After clearing up all the AWR reports we were able to cleanup approx of 5GB space from SYSAUX tablespace.
SYSAUX Tablespace Post AWR Cleanup:

 

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.