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.

Thanks to this blog I found script below wich displays the various sysaux components and their sizes.

SQL> col "Space (M)" for 999,999.99  
SQL> 
SQL> SELECT 
  2    occupant_name,  
  3    round( space_usage_kbytes/1024) "Space (M)",  
  4    schema_name, 
  5    move_procedure
  6  FROM 
  7    v$sysaux_occupants  
  8  ORDER BY 
  9    1  
 10  /
OCCUPANT_NAME          Space (M) SCHEMA_NAME          MOVE_PROCEDURE
-------------------- ----------- -------------------- ----------------------------------------
AO                          1.00 SYS                  DBMS_AW.MOVE_AWMETA
EM                           .00 SYSMAN               emd_maintenance.move_em_tblspc
JOB_SCHEDULER               1.00 SYS
LOGMNR                      6.00 SYSTEM               SYS.DBMS_LOGMNR_D.SET_TABLESPACE
LOGSTDBY                    1.00 SYSTEM               SYS.DBMS_LOGSTDBY.SET_TABLESPACE
ODM                          .00 DMSYS                MOVE_ODM
ORDIM                        .00 ORDSYS
ORDIM/PLUGINS                .00 ORDPLUGINS
ORDIM/SQLMM                  .00 SI_INFORMTN_SCHEMA
SDO                          .00 MDSYS                MDSYS.MOVE_SDO
SM/ADVISOR                 17.00 SYS
[strong]SM/AWR                  3,748.00 SYS[/strong]
SM/OPTSTAT                294.00 SYS
SM/OTHER                   52.00 SYS
STATSPACK                    .00 PERFSTAT
STREAMS                      .00 SYS
TEXT                         .00 CTXSYS               DRI_MOVE_CTXSYS
ULTRASEARCH                  .00 WKSYS                MOVE_WK
WM                           .00 WMSYS                DBMS_WM.move_proc
XDB                          .00 XDB                  XDB.DBMS_XDB.MOVEXDB_TABLESPACE
XSAMD                        .00 OLAPSYS              DBMS_AMD.Move_OLAP_Catalog
XSOQHIST                    1.00 SYS                  DBMS_XSOQ.OlapiMoveProc

22 rows selected.

SQL>   
SQL>

Next thing I retrieve the oldest and latest AWR snapshot, turns out to be 2007…

SQL> SELECT 
  2    snap_id, begin_interval_time, end_interval_time 
  3  FROM 
  4    SYS.WRM$_SNAPSHOT 
  5  WHERE 
  6    snap_id = ( SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT)
  7  UNION 
  8  SELECT 
  9    snap_id, begin_interval_time, end_interval_time 
 10  FROM 
 11    SYS.WRM$_SNAPSHOT 
 12  WHERE 
 13    snap_id = ( SELECT MAX (snap_id) FROM SYS.WRM$_SNAPSHOT)
 14  /
   SNAP_ID BEGIN_INTERVAL_TIME   END_INTERVAL_TIME
---------- --------------------- ---------------------
      7556 [strong]15-09-07 08:44:42,810[/strong] 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

16 thoughts on “Purging SYSAUX tablespace (Purging AWR reports)

  1. Hello Remi ,

    Thanks for your work. It’s wonderful.

    One questions about “Speed up ‘removal’ of old AWR reports”. I can not find doc 852028.1 in metalink. Could you please check it?

  2. Hello Remi,
    thanks a lot for this great post.
    I wanted to get ride of old snapshot, which were not purged automatically by the AWR
    14479
    14-MAR-14 10.00.31.248 PM
    14623
    20-MAR-14 11.00.33.606 PM
    and tried Your first choice with the drop_snapshot_range, which I run for about 30 min and nothing happened.

    Therefore I canceled this and went for Your second choice to drop the tables, which should run much faster(catnoawr.sql). This ends up in error messages, that some tables can’t be dropped with following error:
    ORA-00054: resource busy and acquire with NOWAIT specified

    BUT, when it reaches the “drop table WRH$_SYSSTAT;” then the system hangs unlimited.

    Do You have an idea to solve this?
    Thanks a lot in advance

  3. Its taking a very long time to remove the old snapshots. (“dbms_workload_repository package to remove the AWR snapshots”)
    so i decided to go for Oracle scripts which you suggest to run. But i have Oracle RAC setup and has three nodes. so my question is do i have to run the Scripts in all three nodes or is there any other work a round….Please suggest…Thanks…

Leave a Reply

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