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

Share This
FacebooktwitterlinkedinFacebooktwitterlinkedin

Leave a Reply

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