May 31

How to cleanup SYSAUX tablespace

Below is methods to cleanup SYSAUX Tablespace in Oracle, As part of solving any problem identifying problem is important and here it follows


select *
from v$sysaux_occupants
order by SPACE_USAGE_KBYTES;

and you might find following being last in list
SM/ADVISOR Server Manageability – Advisor Framework
SM/OPTSTAT Server Manageability – Optimizer Statistics History
SM/AWR Server Manageability – Automatic Workload Repository

and below are methods for cleaning for different categories:
SM/OPTSTAT Server Manageability – Optimizer Statistics History

-- Find out your current Stats history retention
select dbms_stats.get_stats_history_retention from dual;
-- change the retention period for not more than 10 days
exec dbms_stats.alter_stats_history_retention(10);
-- Start purging the stats for last 5 yrs approx on day by day basis since this consumes very less undo
begin
for i in REVERSE 10..1500
loop
dbms_stats.purge_stats( SYSDATE - i );
end loop;
end;
/

SM/AWR Server Manageability – Automatic Workload Repository


below plsql will try to clean all snapshots of AWR which are not related to current database
begin
for i in ( select dbid
from SYS.DBA_HIST_DATABASE_INSTANCE
where dbid != ( select dbid from v$database )
order by startup_time )
loop
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE( 1 , 50000 , to_number ( i ) );
end loop;
end;
/

After above steps sysaux tablespace table will have free space which is not release yet
Query to find AWR Tables segments names and size

begin
for i in ( select dbid
from SYS.DBA_HIST_DATABASE_INSTANCE
where dbid != ( select dbid from v$database )
order by startup_time )
loop
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE( 1 , 50000 , to_number ( i ) );
end loop;
/

result of below query will provide sql statements to move table and release space

select 'alter table '||segment_name||' move tablespace SYSAUX;' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='TABLE'

Please make sure to diable awr stats collection before you execute move commands

select 'alter index '||segment_name||' rebuild online parallel (degree 14);' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='INDEX'

Please make sure all indexes are valid and manually collect snapshot to make sure awr snapshot collection is working as expected.

To Be Continued …..

Leave a reply

Your email address will not be published.