:

Shrink the SYSAUX Tablespace in Oracle

The Problem

My SYSAUX tablespace was growing large and I wanted to shrink it on a development environment. First of all I wanted to reduce how long the information was kept in the SYSUAX tablespace. There is a built in Oracle package which is used to modify the snapshot setting for the AWR repository. I used it to try and reduce the time we would keep the information to 2 weeks. You specify the retention period in seconds.

While executing this:

exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>20160);

I got the error:

SQL> exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>20160);
BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>20160); END;
*
ERROR at line 1:
ORA-13541: system moving window baseline size (7862400) greater than retention (1209600)
ORA-06512: at “SYS.DBMS_WORKLOAD_REPOSITORY”, line 174
ORA-06512: at “SYS.DBMS_WORKLOAD_REPOSITORY”, line 222
ORA-06512: at line 1

 

The Solution

You first have to change the baseline window size. This is specified in number of days, so in the example below I am changing the retention period to 1 day.

exec DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size(window_size =>1 );

Now I had this working I want to drop the old snapshots:

Check dba_hist_snapshot to see which snap id range you want to delete:

SELECT MIN(snap_id)
FROM dba_hist_snapshot;
SELECT snap_id
FROM dba_hist_snapshot
WHERE begin_interval_time BETWEEN (sysdate – 14) -1/24 AND (sysdate – 14);
EXEC DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(12926, 16933);

Now you should be able see a big difference in the size of the SYSAUX tablespace. If you are unable to shrink the tablespace because there is data at the ends of the datafiles you can look at the v$sysaux_occupants view to see if you can move the segments and free up the space and then shrink the datafiles afterwards.

Like it, share it...

Category: Database Config


Related Posts

Leave a Reply

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