Cleaning Oracle SYSAUX Tablespace Usage

Oracle Database

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:

sysaux tablespace usage

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:

sysaux tablespace occupants

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).

SYSAUX AWR Usage

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;

AWR retention Period

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:

ORA-13541

 

 

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)
/

AWR retention snapshots

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:
SYSAUX Tablespace post cleanup

 

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

1 Response

  1. Mon says:

    Thank you for the info. After running the 2 SQL statements mentioned above, it freed up 6GB of data but the size of the SYSAUX tablespace is still 26G. After running a query to see what exactly is taking up the space, the AWR is still the biggest culprit. Shouldn’t dropping tables free up a lot more space?

Leave a Reply