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