Resumable Allocation Space no Oracle: Como evitar o cancelamento de execuções devido a problemas de armazenamento

Resumable Allocation Space

Olá, segue meu primeiro tutorial, espero que gostem.

Execuções DML, transações em lote (SQL*Loader) e até mesmo operações DDL (CREATE TABLE… AS, índices, materialized views etc) que envolvem grandes volumes de dados correm o risco de serem canceladas devido a principalmente problemas de armazenamento.

No momento em que a execução falha, devido à falta de espaço no tablespace de UNDO, no tablespace onde a transação está sendo armazenada ou até mesmo QUOTA de usuário, o Oracle lança uma exceção e todos os dados já inseridos são perdidos.

Um recurso introduzido na versão Oracle9i chamado Resumable Allocation Space trata essa exceção mantendo a transação suspensa por um período, (definido em segundos) até que o DBA conceda mais espaço ao usuário. Assim que o problema é resolvido, a execução é retomada a partir do ponto onde parou.

Um trigger pode ser configurado para enviar a mensagem ao DBA avisando sobre a necessidade da manutenção de espaço (exemplo no final do artigo).

 Configuração dos parâmetros

A configuração dessa ferramenta é bastante simples, basta alterar o parâmetro RESUMABLE_TIMEOUT:

SQL> ALTER SYSTEM SET RESUMABLE_TIMEOUT=3600;

O valor é definido em segundos e vem por padrão desativado (RESUMABLE_TIMEOUT=0).

As execuções que receberam a suspensão podem ser listadas através da view DBA_RESUMABLE:

select * from dba_resumable;

Exemplo completo

O resurso também pode ser definido diretamente pela sessão, através do comando ALTER SESSION, confira no exemplo:

Crie um tablespace de 1MB com a opção autoextend off:

SQL> create tablespace my_ts datafile 'my_ts01.dbf' size 1M autoextend off;

Tablespace created.

Habilite o recurso somente para a session (é interessante também definir um nome, porém não é obrigatório):

SQL> ALTER SESSION ENABLE RESUMABLE TIMEOUT 20 NAME 'large insert operation';

Session altered.

O comando emitido na linha acima irá aguardar a resolução do problema por 20 segundos… Note que já há uma indicação na tabela DBA_RESUMABLE fornecendo informações sobre o comando ENABLE RESUMABLE TIMEOUT. Porém alguns campos, referentes à execução do código ainda estão definidos como null:

select user_id, session_id, status, timeout, start_time, suspend_time,  name, sql_text
from dba_resumable;

Criando uma tabela que irá gerar uma suspensão:

SQL> create table XPT tablespace MY_TS as select level "id", rowid "rid" from dual connect by level < 1e9;

Observe que a view DBA_RESUMABLE recebe as informações sobre a instrução:

Depois de 20 segundos, se o problema não for resolvido, o usuário receberá uma mensagem de erro:

ERROR at line 1:

ORA-30032: the suspended (resumable) statement has timed out

ORA-01652: unable to extend temp segment by 8 in tablespace MY_TS

O alert_SID.log também gera uma linha informado sobre a suspenção:

Mon Oct 08 10:19:39 2012

statement in resumable session 'large insert operation' was suspended due to

    ORA-01652: unable to extend temp segment by 8 in tablespace MY_TS

Como dito anteriormente é possível criar um trigger que usando o parâmetro AFTER SUSPEND, é capaz de enviar um alerta por e-mail ou até mesmo um SMS indicando que ocorreu uma falha:

CREATE OR REPLACE TRIGGER resumable_default_timeout

AFTER SUSPEND

ON DATABASE

BEGIN

   DBMS_RESUMABLE.SET_TIMEOUT(10800);

END;