Purging SYSAUX tablespace (Purging AWR reports)

Dump below is of a session clearing the sysaux tablespace.

It turned out that something went wrong with the automatic AWR gathering/purging and the sysaux tablespace became very large.

The script below wich displays the various sysaux components and their sizes.

SQL> col "Space (M)" for 999,999.99
SELECT
   occupant_name,
   round( space_usage_kbytes/1024) "Space (M)",
   schema_name,
   move_procedure
   FROM
   v$sysaux_occupants ORDER BY 1
/

SQL>

Next thing I retrieve the oldest and latest AWR snapshot.

SQL> SELECT
   snap_id, begin_interval_time, end_interval_time
FROM
    SYS.WRM$_SNAPSHOT
WHERE
    snap_id = ( SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT)
    UNION
    SELECT
      snap_id, begin_interval_time, end_interval_time
FROM
    SYS.WRM$_SNAPSHOT
WHERE
    snap_id = ( SELECT MAX (snap_id) FROM SYS.WRM$_SNAPSHOT)
/
   SNAP_ID BEGIN_INTERVAL_TIME   END_INTERVAL_TIME
---------- --------------------- ---------------------
      7556 15-09-07 08:44:42,810 15-09-07 11:01:11,950
     23698 18-08-09 11:00:41,612 18-08-09 12:00:53,074

I prefer SYS.WRM$_SNAPSHOT instead of DBA_HIST_SNAPSHOT because I saw strange results in (Release 10.1.0.5).

Now use the dbms_workload_repository package to remove the AWR snapshots.

BEGIN
dbms_workload_repository.drop_snapshot_range(low_snap_id => 7556, high_snap_id=>15000);
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

http://remidian.com/oracle/purging-sysaux-tablespace-purging-awr-reports.html