tablespace syaux growing too much

Last day i saw SYSAUX tablespace was growing too much. It was due to some Active Session History objects (WRH$) were growing too much:

set linesize 190
col OWNER for a20
col SEGMENT_NAME for a30
select OWNER, SEGMENT_NAME, SEGMENT_TYPE, sum(BYTES)/1024/1024  Mbytes from dba_segments where 
TABLESPACE_NAME like 'SYSAUX' and SEGMENT_TYPE like 'TABLE%' and BYTES>1024*1024*10 and SEGMENT_NAME 
like 'WRH%' group by OWNER, SEGMENT_NAME, SEGMENT_TYPE order by 4;

OWNER                SEGMENT_NAME                   SEGMENT_TYPE                 MBytes
-------------------- ------------------------------ ------------------ ----------------
SYS                  WRH$_PARAMETER                 TABLE PARTITION                  12
SYS                  WRH$_ACTIVE_SESSION_HISTORY    TABLE PARTITION                  13
SYS                  WRH$_SYSMETRIC_HISTORY         TABLE                            14
SYS                  WRH$_SYSSTAT                   TABLE PARTITION                  16
SYS                  WRH$_EVENT_HISTOGRAM           TABLE PARTITION                 432
SYS                  WRH$_SQLSTAT                   TABLE PARTITION                 456
SYS                  WRH$_LATCH                     TABLE PARTITION                 616
                                                                       ----------------
sum                                                                               1,559

It was caused that all this tables and table partitions had orphan records due snapshot are not completely deleted due to some error in MMON processes. . You can delete them by running (this is an example for WRH$_LATCH table, but it works for any other WRH$_ table) :

delete from WRH$_LATCH where snap_id not in (select snap_id from wrm$_snapshot) and dbid=(select DBID from v$database);
commit;

but in some cases, if this table is big, it will fail due to UNDO_TABLESPACE occupation. In that case you can delete this records step by step: Get oldest snapshot on WRM$_SNAPSHOT by running this query:

SELECT min(snap_id), min(BEGIN_INTERVAL_TIME)  FROM sys.wrm$_snapshot;

And once you get oldest record in WRM$_SNAPSHOT, delete records by stretches:

delete from WRH$_LATCH where snap_id <1000 and dbid=(select DBID from v$database); 
commit;
delete from WRH$_LATCH where snap_id <2000 and dbid=(select DBID from v$database); 
commit;
delete from WRH$_LATCH where snap_id <3000 and dbid=(select DBID from v$database); 
commit;
....................................................
delete from WRH$_LATCH where snap_id <10000 and dbid=(select DBID from v$database); 
commit;

This process deletes rows, but not give back space to SYSAUX tablespace. To do that you need to shrink tables:

ALTER TABLE sys.WRH$_LATCH ENABLE ROW MOVEMENT;
ALTER TABLE sys.WRH$_LATCH SHRINK SPACE CASCADE;
ALTER TABLE sys.WRH$_LATCH DISABLE ROW MOVEMENT;

And finally, do not forget to check archived log generation during all this process, this rows deletion generates a big amount of redos, and archivelog filesystem can become full! This view is interesting, it has a list of oracle components allocated on SYSAUX tablespace and stored procedures to move it to another tablespace:

select * from V$SYSAUX_OCCUPANTS;

Finally, i program this shell script in order to shrink all ASH tables from SYSAUX tablespace with orphan keys automatically. It is a good idea to run it under "nohup", but please be carefully with UNDO tablespace, this process may use a lot of UNDO!

#!/bin/ksh
delete_orphan  ()
{
sqlplus " / as sysdba "<<_EOF
DELETE FROM sys.${1} a
WHERE NOT EXISTS (SELECT 1 FROM sys.wrm\$_snapshot b WHERE b.snap_id = a.snap_id AND dbid=(SELECT dbid FROM v\$database) AND b.dbid = a.dbid AND b.instance_number = a.instance_number);
commit;
_EOF
}

shrink_table  ()
{
sqlplus " / as sysdba "<<_EOF
alter table sys.${1} enable row movement;
alter table sys.${1} shrink space cascade;
alter table sys.${1} disable row movement;
_EOF
}

table_list  ()
{
sqlplus -s " / as sysdba "<<_eof > lista.tmp
set heading off
set echo off
set feedback off
set pages 100
select SEGMENT_NAME from dba_segments where TABLESPACE_NAME like 'SYSAUX' and SEGMENT_NAME like 'WRH%' and SEGMENT_TYPE like '%TABLE%' 
group by OWNER, SEGMENT_NAME, SEGMENT_TYPE having sum(BYTES)/1024/1024> 10 order by sum(BYTES)/1024/1024;
_EOF
sed '1d' lista.tmp > lista.txt
rm lista.tmp
}

table_list
	
for tabla in `cat lista.txt`
do
delete_orphan ${tabla}
shrink_table ${tabla}

done