Cleaning Oracle SYSAUX Tablespace Usage - SYSAUX tablespace is 97.51%

Normally the SYSAUX tablespace is more or less stable so it would be smart to check what is eating the space in there. Connected as a DBA user, run the script ${ORACLE_HOME}/rdbms/admin/utlsyxsz to get the current usage of the SYSAUX tablespace and see how it will grow when you change certain parameters for which you are asked to enter values.

 
OR

select 
   * 
from 
   (select 
      owner,segment_name||'~'||partition_name segment_name,bytes/(1024*1024) meg 
   from 
      dba_segments 
      where tablespace_name = 'SYSAUX' 
   order by 
      blocks desc);

In my case, below 1  occupying most of the space :-

1. SM/AWRSM/AWR — It refers to Automatic Workload Repository.Data in this section is retained for a certain amount of time (default 8 days). Setting can be checked through DBA_HIST_WR_CONTROL.

Retrieve the oldest and latest AWR snapshot

SELECTsnap_id, begin_interval_time, end_interval_timeFROMSYS.WRM$_SNAPSHOTWHEREsnap_id = ( SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT)UNIONSELECTsnap_id, begin_interval_time, end_interval_timeFROMSYS.WRM$_SNAPSHOTWHEREsnap_id = ( SELECT MAX (snap_id) FROM SYS.WRM$_SNAPSHOT)/

Now use the dbms_workload_repository package to remove the AWR snapshots.

BEGINdbms_workload_repository.drop_snapshot_range(low_snap_id => 7521, high_snap_id=>7888);END;/

Speed up ‘removal’ of old AWR reports

@#$%^&*()_ removing the entries takes ages and fails on undo errors … Metalink note Doc ID: 852028.1 states that I can safely remove the AWR metadata tables and recreate them.If none of the above suits as everything is set proper then consider clean up and rebuild AWR repository to clear all the space.

SQL> connect / as sysdba 
SQL> @?/rdbms/admin/catnoawr.sql 
SQL> @?/rdbms/admin/catawrtb.sql

Reference :-WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (Doc ID 387914.1)Suggestions if Your SYSAUX Tablespace Grows Rapidly or Too Large (Doc ID 1292724.1)