Cleaning Sysaux tablespace

Cleaning SYSAUX tablespace

1. Running @?/rdbms/admin/awrinfo.sql –> Detailed Info like Schema breakdown, SYSAUX occupants space usage etc.

This will give whats using most.

So Narrow down from here. In case its AWR with WRH tables. Then check the snapshot max and min values of particular tables

SQL> select min(snap_id),MAX(snap_id) from dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)
———— ————
49032 49210

SQL> select min(snap_id),MAX(snap_id) from WRH$_ACTIVE_SESSION_HISTORY;

MIN(SNAP_ID) MAX(SNAP_ID)
———— ————
45912 49210

SQL> select min(snap_id),MAX(snap_id) from WRH$_EVENT_HISTOGRAM;

MIN(SNAP_ID) MAX(SNAP_ID)
———— ————
20121 49210

SQL> select min(snap_id),MAX(snap_id) from WRH$_SQL_BIND_METADATA;

MIN(SNAP_ID) MAX(SNAP_ID)
———— ————
19976 49260

In the above case something wrong with event_histogram and sql_bind_metadata.

These have way old information. This is because oracle purge job runs in maintainance windows and if query is taking too long to purge it will skip it. Thats why these are piled up.

So check the partition sof these tables to get a clear idea

if you see 2 partitions for each table then run

SQL> alter session set "_swrf_test_action" = 72; ( force split the partition operation using command)



this will create a new partition

We have to delete old snapshots to reclaim space in the above case delete from 19976 to 45912. Partition name has db id init. Note down these 3 values

EXEC DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE( 19976,45912,2779876350);

1st one is begin, 2nd one is end and 3rd one is dbid.

Run this..This is resource intensive. Make sure you have sufficient undo tbs.


Tha above did not work for me as we dont have sufficient undo TBS

So i created partitions on the tables that have old snapshots and deleted manually




Splitting Partition and dropping partition

ALTER TABLE WRH$_PARAMETER
 SPLIT PARTITION   WRH$_PARAME_3251756137_17280 AT
 (3251756137, 27679)
 INTO (PARTITION   WRH$_PARAME_3251756137_ZZZ
       TABLESPACE SYSAUX
       PCTFREE    1
       INITRANS   1
       MAXTRANS   255
       STORAGE    (
                   INITIAL          64K
                   NEXT             1M
                   MAXSIZE          UNLIMITED
                   MINEXTENTS       1
                   BUFFER_POOL      DEFAULT
                   FLASH_CACHE      DEFAULT
                   CELL_FLASH_CACHE DEFAULT
                  ),
       PARTITION   WRH$_PARAME_3251756137_17280)
  UPDATE GLOBAL INDEXES;

ALTER TABLE WRH$_SEG_STAT
 SPLIT PARTITION   WRH$_SEG_ST_3251756137_17280 AT
 (3251756137, 27679)
 INTO (PARTITION   WRH$_SEG_ST_3251756137_ZZZ
       TABLESPACE SYSAUX
       PCTFREE    1
       INITRANS   1
       MAXTRANS   255
       STORAGE    (
                   INITIAL          64K
                   NEXT             1M
                   MAXSIZE          UNLIMITED
                   MINEXTENTS       1
                   BUFFER_POOL      DEFAULT
                   FLASH_CACHE      DEFAULT
                   CELL_FLASH_CACHE DEFAULT
                  ),
       PARTITION   WRH$_SEG_ST_3251756137_17280)
  UPDATE GLOBAL INDEXES;

ALTER TABLE WRH$_DLM_MISC
 SPLIT PARTITION   WRH$_DLM_MI_3251756137_17280 AT
 (3251756137, 27679)
 INTO (PARTITION   WRH$_DLM_MI_3251756137_ZZZ
       TABLESPACE SYSAUX
       PCTFREE    1
       INITRANS   1
       MAXTRANS   255
       STORAGE    (
                   INITIAL          64K
                   NEXT             1M
                   MAXSIZE          UNLIMITED
                   MINEXTENTS       1
                   BUFFER_POOL      DEFAULT
                   FLASH_CACHE      DEFAULT
                   CELL_FLASH_CACHE DEFAULT
                  ),
       PARTITION   WRH$_DLM_MI_3251756137_17280)
  UPDATE GLOBAL INDEXES;

ALTER TABLE WRH$_SERVICE_STAT
 SPLIT PARTITION    WRH$_SERVIC_3251756137_17280 AT
 (3251756137, 27679)
 INTO (PARTITION    WRH$_SERVIC_3251756137_ZZZ
       TABLESPACE SYSAUX
       PCTFREE    1
       INITRANS   1
       MAXTRANS   255
       STORAGE    (
                   INITIAL          64K
                   NEXT             1M
                   MAXSIZE          UNLIMITED
                   MINEXTENTS       1
                   BUFFER_POOL      DEFAULT
                   FLASH_CACHE      DEFAULT
                   CELL_FLASH_CACHE DEFAULT
                  ),
       PARTITION    WRH$_SERVIC_3251756137_17280)
  UPDATE GLOBAL INDEXES;

ALTER TABLE WRH$_TABLESPACE_STAT
 SPLIT PARTITION   WRH$_TABLES_3251756137_17280 AT
 (3251756137, 27679)
 INTO (PARTITION   WRH$_TABLES_3251756137_ZZZ
       TABLESPACE SYSAUX
       PCTFREE    1
       INITRANS   1
       MAXTRANS   255
       STORAGE    (
                   INITIAL          64K
                   NEXT             1M
                   MAXSIZE          UNLIMITED
                   MINEXTENTS       1
                   BUFFER_POOL      DEFAULT
                   FLASH_CACHE      DEFAULT
                   CELL_FLASH_CACHE DEFAULT
                  ),
       PARTITION   WRH$_TABLES_3251756137_17280)
  UPDATE GLOBAL INDEXES;

ALTER TABLE WRH$_OSSTAT
 SPLIT PARTITION   WRH$_OSSTAT_3251756137_17280 AT
 (3251756137, 27679)
 INTO (PARTITION   WRH$_OSSTAT_3251756137_ZZZ
       TABLESPACE SYSAUX
       PCTFREE    1
       INITRANS   1
       MAXTRANS   255
       STORAGE    (
                   INITIAL          64K
                   NEXT             1M
                   MAXSIZE          UNLIMITED
                   MINEXTENTS       1
                   BUFFER_POOL      DEFAULT
                   FLASH_CACHE      DEFAULT
                   CELL_FLASH_CACHE DEFAULT
                  ),
       PARTITION   WRH$_OSSTAT_3251756137_17280)
  UPDATE GLOBAL INDEXES;

ALTER TABLE  WRH$_SYS_TIME_MODEL
 SPLIT PARTITION   WRH$_SYS_TI_3251756137_17280 AT
 (3251756137, 27679)
 INTO (PARTITION   WRH$_SYS_TI_3251756137_ZZZ
       TABLESPACE SYSAUX
       PCTFREE    1
       INITRANS   1
       MAXTRANS   255
       STORAGE    (
                   INITIAL          64K
                   NEXT             1M
                   MAXSIZE          UNLIMITED
                   MINEXTENTS       1
                   BUFFER_POOL      DEFAULT
                   FLASH_CACHE      DEFAULT
                   CELL_FLASH_CACHE DEFAULT
                  ),
       PARTITION   WRH$_SYS_TI_3251756137_17280)
  UPDATE GLOBAL INDEXES;

ALTER TABLE  WRH$_SERVICE_WAIT_CLASS
 SPLIT PARTITION   WRH$_SERVIC_3251756137_17280 AT
 (3251756137, 27679)
 INTO (PARTITION   WRH$_SERVIC_3251756137_ZZZ
       TABLESPACE SYSAUX
       PCTFREE    1
       INITRANS   1
       MAXTRANS   255
       STORAGE    (
                   INITIAL          64K
                   NEXT             1M
                   MAXSIZE          UNLIMITED
                   MINEXTENTS       1
                   BUFFER_POOL      DEFAULT
                   FLASH_CACHE      DEFAULT
                   CELL_FLASH_CACHE DEFAULT
                  ),
       PARTITION   WRH$_SERVIC_3251756137_17280)
  UPDATE GLOBAL INDEXES;

ALTER TABLE WRH$_INST_CACHE_TRANSFER
 SPLIT PARTITION   WRH$_INST_C_3251756137_17280 AT
 (3251756137, 27679)
 INTO (PARTITION   WRH$_INST_C_3251756137_ZZZ
       TABLESPACE SYSAUX
       PCTFREE    1
       INITRANS   1
       MAXTRANS   255
       STORAGE    (
                   INITIAL          64K
                   NEXT             1M
                   MAXSIZE          UNLIMITED
                   MINEXTENTS       1
                   BUFFER_POOL      DEFAULT
                   FLASH_CACHE      DEFAULT
                   CELL_FLASH_CACHE DEFAULT
                  ),
       PARTITION   WRH$_INST_C_3251756137_17280)
  UPDATE GLOBAL INDEXES;

ALTER TABLE WRH$_INTERCONNECT_PINGS
 SPLIT PARTITION   WRH$_INTERC_3251756137_17280 AT
 (3251756137, 27679)
 INTO (PARTITION   WRH$_INTERC_3251756137_ZZZ
       TABLESPACE SYSAUX
       PCTFREE    1
       INITRANS   1
       MAXTRANS   255
       STORAGE    (
                   INITIAL          64K
                   NEXT             1M
                   MAXSIZE          UNLIMITED
                   MINEXTENTS       1
                   BUFFER_POOL      DEFAULT
                   FLASH_CACHE      DEFAULT
                   CELL_FLASH_CACHE DEFAULT
                  ),
       PARTITION   WRH$_INTERC_3251756137_17280)
  UPDATE GLOBAL INDEXES;

ALTER TABLE WRH$_MVPARAMETER
 SPLIT PARTITION   WRH$_MVPARA_3251756137_17280 AT
 (3251756137, 27679)
 INTO (PARTITION   WRH$_MVPARA_3251756137_ZZZ
       TABLESPACE SYSAUX
       PCTFREE    1
       INITRANS   1
       MAXTRANS   255
       STORAGE    (
                   INITIAL          64K
                   NEXT             1M
                   MAXSIZE          UNLIMITED
                   MINEXTENTS       1
                   BUFFER_POOL      DEFAULT
                   FLASH_CACHE      DEFAULT
                   CELL_FLASH_CACHE DEFAULT
                  ),
       PARTITION   WRH$_MVPARA_3251756137_17280)
  UPDATE GLOBAL INDEXES;

select * from dba_ind_partitions where status='UNUSABLE';
alter index WRH$_EVENT_HISTOGRAM_PK rebuild partition WRH$_EVENT__3251756137_17280;
alter index WRH$_SYSSTAT_PK rebuild partition WRH$_SYSSTA_3251756137_0;
alter index WRH$_SYSSTAT_PK rebuild partition WRH$_SYSSTA_3251756137_17280;
alter index WRH$_SYSSTAT_PK rebuild partition WRH$_SYSSTAT_MXDB_MXSN;



alter table WRH$_DB_CACHE_ADVICE shrink space;
alter table WRH$_DLM_MISC shrink space;
alter table WRH$_EVENT_HISTOGRAM shrink space;
alter table WRH$_FILESTATXS shrink space;
alter table WRH$_INST_CACHE_TRANSFER shrink space;
alter table WRH$_INTERCONNECT_PINGS shrink space;
alter table WRH$_LATCH shrink space;
alter table WRH$_LATCH_CHILDREN shrink space;
alter table WRH$_LATCH_MISSES_SUMMARY shrink space;
alter table WRH$_LATCH_PARENT shrink space;
alter table WRH$_MVPARAMETER shrink space;
alter table WRH$_OSSTAT shrink space;
alter table WRH$_PARAMETER shrink space;
alter table WRH$_ROWCACHE_SUMMARY shrink space;
alter table WRH$_SEG_STAT shrink space;
alter table WRH$_SERVICE_STAT shrink space;
alter table WRH$_SERVICE_WAIT_CLASS shrink space;
alter table WRH$_SGASTAT shrink space;
alter table WRH$_SQLSTAT shrink space;
alter table WRH$_SYSSTAT shrink space;
alter table WRH$_SYSTEM_EVENT shrink space;
alter table WRH$_SYS_TIME_MODEL shrink space;
alter table WRH$_TABLESPACE_STAT shrink space;
alter table WRH$_WAITSTAT shrink space;

select 'Alter table  '||TABLE_NAME||' DROP PARTITION '||PARTITION_NAME||' update global indexes;' from dba_tab_partitions where table_name in ('WRH$_DLM_MISC'
,'WRH$_INST_CACHE_TRANSFER'
,'WRH$_INTERCONNECT_PINGS'
,'WRH$_MVPARAMETER'
,'WRH$_OSSTAT'
,'WRH$_PARAMETER'
,'WRH$_SEG_STAT'
,'WRH$_SERVICE_STAT'
,'WRH$_SERVICE_WAIT_CLASS'
,'WRH$_SYS_TIME_MODEL'
,'WRH$_TABLESPACE_STAT'
) and partition_name like '%17280';