remidian.com

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
SM/AWR                  3,748.00 SYS
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 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