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. Eric says:

    Thanks for this info. It has saved me time.

  2. Ming says:

    This is awesome. Thank you!
    ming

  3. Eric Vasquez says:

    Thank you a lot for this interesting tips, it worked fine for me and save me time.

  4. Priya says:

    I just followed this and it saved lot of my time. Thanks a lot.

  5. Mohamed says:

    Thanks for theses tips ! It was very helpfull !

  6. Kenneth Rowe says:

    That worked great. The tablespace used by SM/AWR dropped from 343 mb to 147 mb.

  7. Kevin Zhang says:

    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?

  8. iuliana says:

    Great post, thank you!

  9. Paul says:

    The recreation of AWR can be found in
    “How to Recreate The AWR ( AUTOMATIC WORKLOAD ) Repository ? (Doc ID 782974.1)”

  10. Klaus says:

    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

  11. Frank Krüger says:

    Very good, all statements are correct

  12. Naveen kallumarri says:

    Great post, thank you!

  13. laurent DHILLY says:

    in 2016 this post is still a very good post
    Many thanks

    1. remivisser says:

      Thanks Laurent :)

  14. awais says:

    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…

  15. Bob says:

    Check Oracle doc 782974.1

Leave a Reply

Helpful? - leave your note below so I can brag

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>