TABLESPACE SYSAUX crescendo excepcionalmente

1 - TABELA com 50GB na sysaux
Motivo - Armazena informações de snapshots do awr (automatic workload repository), que são relativos a relatórios automáticos relacionados a estatísticas do oracle.
Bug relatado no Metalink: 
Table/Index (partition) Growth Is Far More Than Expected (Doc ID 729149.1)
Devido a snapshots antigos, é necessário remover os mesmos, efetuar a limpeza da tabela e fazer o shrink da tabela.

Procedimento:

1.1 ver as configurações das estatísticas da instância:
col statistics_name for a40
SELECT STATISTICS_NAME, SESSION_STATUS, SYSTEM_STATUS, ACTIVATION_LEVEL
FROM v$statistics_level  ORDER BY 3 ;


2 - identificando a tabela:
== verificando o tamanho ==
set lines 200
col SEGMENT_NAME format a60
col 
select * from (select 
owner,segment_name||'~'||partition_name segment_name,bytes/(1024*1024) size_m
from dba_segments where tablespace_name = 'SYSAUX' 
ORDER BY BLOCKS desc )
where rownum < 3;


OWNER                SEGMENT_NAME                                                     SIZE_M
-------------------- ------------------------------------------------------------ ----------
SYS                  WRH$_LATCH_CHILDREN~WRH$_LATCH__1599336177_38253                  25567
SYS                  WRH$_LATCH_CHILDREN_PK~WRH$_LATCH__1599336177_38253               20695


3 - Range dos snapshots:
SQL> select min(snap_id), max(snap_id), snap_level from dba_hist_snapshot group by snap_level;

MIN(SNAP_ID) MAX(SNAP_ID) SNAP_LEVEL
------------ ------------ ----------
       40138        40347          2

4 - procedimento de limpeza:
4.1 - remoção dos snapshots:

SQL> execute dbms_workload_repository.drop_snapshot_range(40138, 40340);


4.2 - Para reduzir o tamanho dos blocos, reorganizando a tabela e liberar espaço, executar a seguinte procedure, cujo conteúdo faz a reorganização das linhas e assim que terminar, faz o shrink das tabelas do que armazenam as informações do AWR:

declare
v_sql1 varchar2(2000);
v_sql2 varchar2(2000);
begin
for rec in (select TABLE_NAME from dba_tables where TABLE_NAME like 'WRH$%') loop
v_sql1 := 'alter table ' || rec.TABLE_NAME || ' enable row movement ' ;
execute immediate v_sql1;
v_sql2 := 'alter table ' || rec.TABLE_NAME || ' shrink space cascade' ;
execute immediate v_sql2;
end loop;
end ;
/


Obs: pode ser feito também manualmente, porém não há previsão de tempo e por isso pode ser executado em nohup , basta criar um script.

Fontes:
http://colbran.co.za/wordpress/2010/10/01/sysman-tablespace-grows-excessively/
Metalink: Table/Index (partition) Growth Is Far More Than Expected (Doc ID 729149.1)