Tablespace SYSAUX crescendo excepcionalmente DBCS

Hoje iremos avaliar quais objetos estão crescendo excepcionalmente na tablespace SYSAUX ambiente DB systems.

1 – Verificando quais objetos estão crescendo muito rápido.

column  schema_name             format a32
column  occupant_name           format a32
column  move_procedure          format a45
column  space_usage_kbytes      format 999,999,999
  
select 
        schema_name, occupant_name, move_procedure, occupant_desc, space_usage_kbytes 
from 
        v$sysaux_occupants
order by 
        space_usage_kbytes
;

SQL

2 – Verificando o maior segmento.

select owner,segment_name,segment_type,(bytes/1024/1024) MB from dba_segments
where tablespace_name='SYSAUX' order by MB desc;

SQL

3 – Verificando alto consumo de espaço pela coluna LOB na tabela SYS.SCHEDULER$_JOB_OUTPUT.

SELECT * FROM dba_lobs WHERE segment_name = 'SYS_LOB0000009291C00003$$';

SQL

4 – Quantidade de linhas na tabela.

select count(*) from SYS.SCHEDULER$_JOB_OUTPUT;

SQL

5 – Purge para limpeza geral.

SQL> exec dbms_scheduler.purge_log(0, 'JOB_AND_WINDOW_LOG');
SQL> truncate table scheduler$_job_output;

SQL

6 – MOS completo para correção do problema:

SYSAUX Tablespace Growing Due to SYS.SCHEDULER$_JOB_OUTPUT LOB Column (Doc ID 2095104.1)
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=33521469745935&parent=EXTERNAL_SEARCH&sourceId=PROBLEM&id=2095104.1&_afrWindowMode=0&_adf.ctrl-state=sg6hti2x_4

SQL

Leave a Reply Cancel reply