Gerenciando a alocação de espaço recuperável no Oracle (RESUMABLE SPACE ALLOCATION)

Olá,

A ativação da alocação de espaço recuperável (resumable space allocation) pode ser um recurso útil ao DBA para operações de importação, carga de dados e processos em lote que manipulam muitos dados. Geralmente por serem operações longas, as mesmas podem ocasionar erros de banco de dados pelo fato do mesmo não conseguir alocar extensões suficientes para um segmento dentro de um tablespace. Para um DBA desavisado isto pode acarretar em um desperdício de tempo imenso pelo fato de toda a transação ser abortada. Imagine uma operação de (INSERT ... SELECT) que manipula milhões de linhas em uma tabela: Dependendo do banco de dados, esta operação poderá levar horas para terminar e, caso após 40 minutos de processamento, um erro ser exibido na tela informando que não foi possível estender o segmento devido à falta de espaço no tablespace, ou porque a cota de espaço no tablespace foi excedida, com certeza é desanimador. Em versões anteriores ao Oracle 9i, esta operação precisaria ser re-executada, mas a partir do Oracle 9i estas operações que abortam devido à problemas de armazenamento, podem ser suspensas e resumidas assim que o problema for solucionado sem ocasionar a perda de dados do que já foi processado. Para ficar mais claro, a figura abaixo mostra este tipo de situação:
 

Neste artigo irei demonstrar através de exemplos práticos um pouco da utilização deste recurso e como tirar vantagem dele. O script dbmsres.sql geralmente localizado no diretório $ORACLE_HOME/rdbms/admin e executado durante a criação do banco de dados, é responsável por criar o pacote DBMS_RESUMABLE que disponibiliza algumas funções e procedimentos. As views de dicionário de dados DBA_RESUMABLE e USER_RESUMABLE também estão disponíveis para acesso quando necessário.

Em resumo, uma sentença ou operação DML/DDL pode ser suspensa em vez de ser abortada por ocasião de um erro, permitindo que o erro seja solucionado a tempo. Com a ativação deste recurso, uma sessão fica em estado de espera (suspensa) caso os seguintes erros venham a ocorrer durante a execução de uma operação:

• ORA-01536: cota de espaço excedida para o tablespace ''
• ORA-01562: falha ao estender número de segmento de rollback
• ORA-01628: # máx. de extensões () atingido para o segmento de rollback
• ORA-01631: # máx. de extensões () atingido na tabela.
• ORA-01650: não é possível estender o segmento de rollback de no tablespace
• ORA-01652: não é possível estender o segmento temp. em no tablespace
• ORA-01653: não é possível estender a tabela . em no tablespace
• ORA-01654: não é possível estender o índice . em no tablespace
• ORA-01658: não é possível criar a extensão INITIAL para o segmento no tablespace
• ORA-01659: não é possível alocar MINEXTENTS além de no tablespace

Caso o problema não seja resolvido a tempo, então a sessão sairá do estado de espera e o erro "ORA-30032: a instrução suspensa (recuperável) sofreu timeout" será emitido para a sessão que iniciou a operação. Existem diferentes tipos de operações que podem tirar proveito do gerenciamento de espaço recuperável, ou seja, operações que podem ser suspensas e resumidas:

• SELECT - Caso a consulta necessite de espaço temporário para operações de classificação
• INSERT, UPDATE, DELETE - Estas operações podem ser suspensas caso as mesmas necessitem de espaço no tablespace
• INSERT ... AS SELECT - Estas operações podem ser suspensas caso as mesmas necessitem de espaço no tablespace
• IMP utility - Este utilitário possui recurso para suspender e resumir a operação de importação
• SQL*Loader - Suporta operações de recuperação
• CREATE TABLE ... AS SELECT - Suporta gerenciamento de espaço recuperável
• CREATE INDEX - Suporta gerenciamento de espaço recuperável
• ALTER INDEX ... REBUILD - Suporta gerenciamento de espaço recuperável
• ALTER TABLE ... MOVE PARTITION - Suporta gerenciamento de espaço recuperável
• ALTER TABLE ... SPLIT PARTITION - Suporta gerenciamento de espaço recuperável
• ALTER INDEX ... REBUILD PARTITION - Suporta gerenciamento de espaço recuperável
• ALTER INDEX ... SPLIT PARTITION - Suporta gerenciamento de espaço recuperável
• CREATE MATERIALIZED VIEW - Suporta gerenciamento de espaço recuperável
• CREATE MATERIALIZED VIEW LOG - Suporta gerenciamento de espaço recuperável

No caso do utilitário de importação (impdp) Import Datapump, é possível resumir a operação de importação caso o processo emita a mensagem abaixo:

impdp - ORA-39171: Job está enfrentando uma espera que pode ser retomada.

No caso do utilitário (imp) tradicional podemos utilizar os parâmetros abaixo:

C:\>imp help=y

Palavra-chave       Descrição (Default)
-----------------------------------------------------------------------------------
RESUMABLE           suspender quando um erro relacionado a espaço for encontrado(N)
RESUMABLE_NAME      string de texto usada para identificar instrução recuperável
RESUMABLE_TIMEOUT   tempo de espera para RESUMABLE

Ativando o Recurso para Alocação de Espaço Recuperável

No Oracle 9i este recurso pode ser ativado apenas em nível de sessão. No Oracle 10g este recurso pode ser ativado tanto em nível de sistema quanto em nível de sessão. Um novo parâmetro de inicialização RESUMABLE_TIMEOUT que pode ser alterado dinamicamente, foi introduzido no Oracle 10g para armazenar o tempo (valor em segundos) que uma sessão deverá ficar suspensa no caso de a mesma detectar que a operação corrente gerou uma exceção por falta de espaço no tablespace. O valor padrão deste parâmetro é 0 (zero), ou seja, este recurso é desabilitado por padrão.

Exemplo:

SQL> alter system set resumable_timeout = 3600; -- 1 hora
SQL> alter system set resumable_timeout = 7200; -- 2 horas
SQL> alter session enable resumable; -- 2 horas
SQL> alter session enable resumable timeout 600; -- 10 minutos

Depois de todas estas explicações, vamos então a um exemplo prático. Para esta demonstração utilizarei o Oracle Daabase Express Edition 10g release 2.

-- Irei criar um tablespace de teste com 1 MB de tamanho
SYS> create tablespace tbs_teste
  2  logging
  3  datafile 'c:\oraclexe\oradata\xe\teste01.dbf' size 1m
  4  extent management local
  5  segment space management auto;

Tablespace criado.

-- Verificando que o mesmo não é auto-extensível
SYS> select tablespace_name,
  2  bytes,
  3  autoextensible
  4  from dba_data_files
  5  where tablespace_name = 'TBS_TESTE';

TABLESPACE_NAME                     BYTES AUT
------------------------------ ---------- ---
TBS_TESTE                         1048576 NO

-- Verificando o valor do parâmetro resumable_timeout
SYS> show parameter resumable_timeout

NAME                           TYPE        VALUE
------------------------------ ----------- ------------------
resumable_timeout              integer     0

-- Criando um schema de teste
SYS> create user scott identified by tiger
  2  default tablespace tbs_teste
  3  quota unlimited on tbs_teste;

Usuário criado.

-- Concedendo os privilégios necessários
SYS> grant connect,resource,resumable to scott;

Concessão bem-sucedida.

-- Conectando com o usuário SCOTT
SYS> connect scott/tiger;
Conectado.

-- criando uma tabela de teste
SCOTT> create table t1 (id number);

Tabela criada.

Realizando teste sem ativar o gerenciamento de espaço recuperável

-- Inserindo registros na tabela
SCOTT> insert into t1 select level from dual connect by level <= 100000;
insert into t1 select level from dual connect by level <= 100000
            *
ERRO na linha 1:
ORA-01653: não é possível estender a tabela SCOTT.T1 em 8 no tablespace TBS_TESTE

Podemos perceber acima que o erro ORA-01653 foi emitido pelo fato de não haver espaço livre suficiente no tablespace TBS_TESTE para acomodar os registros inseridos na tabela T1.

Realizando teste após ativar o gerenciamento de espaço recuperável

SCOTT> set time on

-- Ativando o recurso definindo 60 segundos de timeout
09:01:05 SCOTT> alter session enable resumable timeout 60;

Sessão alterada.

-- Realizando a operação de inserção de dados novamente
09:01:05 SCOTT> insert into t1 select level from dual connect by level <= 100000;
[aguardando ...]

Podemos perceber acima que o erro ORA-01653 não foi emitido como no exemplo anterior. Ao invés disso, a sessão foi colocada em estado de espera (suspensa) por 60 segundos para a resolução do problema. Abaixo irei abrir uma outra sessão de forma a verificar o que realmente está acontecendo.

Sessão SYS
----------

-- Irei consultar a view DBA_RESUMABLE
SYS> set serveroutput on
SYS> exec print_table('select * from dba_resumable');
USER_ID            : 48
SESSION_ID         : 29
INSTANCE_ID        : 1
COORD_INSTANCE_ID  :
COORD_SESSION_ID   :
STATUS             : SUSPENDED
TIMEOUT            : 60
START_TIME         : 21/08/08 09:01:05
SUSPEND_TIME       : 21/08/08 09:01:06
RESUME_TIME        :
NAME               : User SCOTT(48), Session 29, Instance 1
SQL_TEXT           : insert into t1 select level from dual connect by level <= 100000
ERROR_NUMBER       : 1653
ERROR_PARAMETER1   : SCOTT
ERROR_PARAMETER2   : T1
ERROR_PARAMETER3   : 8
ERROR_PARAMETER4   : TBS_TESTE
ERROR_PARAMETER5   :
ERROR_MSG          : ORA-01653: não é possível estender a tabela SCOTT.T1 em 8 ...
-----------------

Procedimento PL/SQL concluído com sucesso.

De acordo com o resultado acima, podemos ver que a sessão de ID 29 foi suspensa de forma a evitar que o erro ORA-01653 fosse emitido. Neste caso o DBA terá 60 segundos para resolver o problema. Abaixo irei consultar a view dinâmica de desempenho V$SESSION para confirmar esta informação:

SYS> select username,status,sid,serial#,event from v$session where username='SCOTT';

USERNAME    STATUS        SID   SERIAL# EVENT
----------- -------- -------- --------- ---------------------------------------------
SCOTT       ACTIVE         29        36 statement suspended, wait error to be cleared

Na coluna EVENT acima, temos a informação de que uma instrução foi suspensa e que a sessão está aguardando para que o erro seja solucionado.

Quando uma sessão é suspensa, uma linha também é adicionada na view dinâmica de desempenho V$SESSION_WAIT. Abaixo podemos ver que já se passaram 20 segundos, ou seja, o DBA terá agora apenas 40 segundos para resolver o problema (aumentar o arquivo de dados ou habilitar a extensão automática do mesmo):

SYS> select sid,event,seconds_in_wait from v$session_wait where sid=29;

       SID EVENT                                                SECONDS_IN_WAIT
---------- ---------------------------------------------------- ---------------
        29 statement suspended, wait error to be cleared                     20

Apenas para fins de demonstração, a query abaixo nos mostra que a transação ainda continua ativa:

SYS> exec print_table('select username,sid,serial#,segment_name,t.status,t.start_time,
                       sa.sql_text from v$session s, v$transaction t,
                       dba_rollback_segs r, v$sqlarea sa 
                       where  s.taddr = t.addr and 
                       t.xidusn = r.segment_id(+) and 
                       s.sql_address = sa.address(+)');

USERNAME          : SCOTT
SID               : 29
SERIAL#           : 36
SEGMENT_NAME      : _SYSSMU4$
STATUS            : ACTIVE
START_TIME        : 21/08/08 09:01:05
SQL_TEXT          : insert into t1 select level from dual connect by level <= 100000
-----------------

Procedimento PL/SQL concluído com sucesso.

Voltando para a sessão que emitiu a instrução, e supondo que o problema não foi solucionado em tempo hábil, podemos ver abaixo que após passados os 60 segundos a operação sofreu timeout:

Sessão SCOTT
------------

insert into t1 select level from dual connect by level <= 100000
*
ERRO na linha 1:
ORA-30032: a instrução suspensa (recuperável) sofreu timeout
ORA-01653: não é possível estender a tabela SCOTT.T1 em 8 no tablespace TBS_TESTE

Consultando novamente a view DBA_RESUMABLE utilizando a outra sessão, podemos ver abaixo que o status da operação foi redefinida para TIMEOUT, indicando que a operação DML iniciada pela sessão 29 não foi concluída com sucesso.

Sessão SYS
----------

SYS> exec print_table('select * from dba_resumable');
USER_ID            : 48
SESSION_ID         : 29
INSTANCE_ID        : 1
COORD_INSTANCE_ID  :
COORD_SESSION_ID   :
STATUS             : TIMEOUT
TIMEOUT            : 60
START_TIME         : 21/08/08 09:01:06
SUSPEND_TIME       : 21/08/08 09:01:06
RESUME_TIME        :
NAME               : User SCOTT(48), Session 29, Instance 1
SQL_TEXT           : insert into t1 select level from dual connect by level <= 100000
ERROR_NUMBER       : 1653
ERROR_PARAMETER1   : SCOTT
ERROR_PARAMETER2   : T1
ERROR_PARAMETER3   : 8
ERROR_PARAMETER4   : TBS_TESTE
ERROR_PARAMETER5   :
ERROR_MSG          : ORA-01653: não é possível estender a tabela SCOTT.T1 em 8 ...
-----------------

Procedimento PL/SQL concluído com sucesso.

Abaixo, podemos ver que o arquivo de log alerta também armazena informações relacionadas às operações recuperáveis geradas pelas sessões:

Conteúdo do Arquivo de Log de Alerta
------------------------------------
Thu Aug 21 09:01:06 2008
statement in resumable session 'User SCOTT, Session 29, Instance 1' was suspended due
to ORA-01653: não é possível estender a tabela SCOTT.T1 em 8 no tablespace TBS_TESTE
Thu Aug 21 09:02:08 2008
statement in resumable session 'User SCOTT, Session 29, Instance 1' was timed out

Solucionando o Problema antes de estourar o tempo limite

Irei agora demonstrar através de eventos seqüenciais (1,2,3,4 e 5) como solucionar o problema de espaço no tablespace TBS_TESTE ocorrido no exemplo anterior.

Sessão Scott
------------

-- [evento 1]
SCOTT> alter session enable resumable timeout 60;

Sessão alterada.

-- [evento 2]
SCOTT> insert into t1 select level from dual connect by level <= 100000;
[aguardando ...]

-- Linhas inseridas após a ativação da extensão automática realizada no [evento 3]
100000 linhas criadas.

-- [evento 5]
SCOTT> alter session disable resumable;

Sessão alterada.


Sessão SYS
----------

-- [evento 3]
SYS> alter database datafile 5 autoextend on;

Banco de dados alterado.

-- [evento 4]
SYS> set serveroutput on
SYS> exec print_table('select * from dba_resumable');
USER_ID            : 48
SESSION_ID         : 29
INSTANCE_ID        : 1
COORD_INSTANCE_ID  :
COORD_SESSION_ID   :
STATUS             : NORMAL
TIMEOUT            : 60
START_TIME         : 21/08/08 11:34:53
SUSPEND_TIME       :
RESUME_TIME        : 21/08/08 11:35:01
NAME               : User SCOTT(48), Session 26, Instance 1
SQL_TEXT           : insert into t1 select level from dual connect by level <= 100000
ERROR_NUMBER       : 0
ERROR_PARAMETER1   :
ERROR_PARAMETER2   :
ERROR_PARAMETER3   :
ERROR_PARAMETER4   :
ERROR_PARAMETER5   :
ERROR_MSG          :
-----------------

Procedimento PL/SQL concluído com sucesso.

Utilizando o pacote DBMS_RESUMABLE

Abaixo estão listadas algumas das funções e procedimentos oferecidas por este pacote e que podem ser úteis ao DBA quando necessário:

SYS> desc dbms_resumable
PROCEDURE ABORT
 Nome do Argumento              Tipo                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SESSIONID                      NUMBER                  IN

FUNCTION GET_SESSION_TIMEOUT RETURNS NUMBER
 Nome do Argumento              Tipo                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SESSIONID                      NUMBER                  IN

FUNCTION GET_TIMEOUT RETURNS NUMBER

PROCEDURE SET_SESSION_TIMEOUT
 Nome do Argumento              Tipo                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SESSIONID                      NUMBER                  IN
 TIMEOUT                        NUMBER                  IN

PROCEDURE SET_TIMEOUT
 Nome do Argumento              Tipo                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 TIMEOUT                        NUMBER                  IN

FUNCTION SPACE_ERROR_INFO RETURNS BOOLEAN
 Nome do Argumento              Tipo                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ERROR_TYPE                     VARCHAR2                OUT
 OBJECT_TYPE                    VARCHAR2                OUT
 OBJECT_OWNER                   VARCHAR2                OUT
 TABLE_SPACE_NAME               VARCHAR2                OUT
 OBJECT_NAME                    VARCHAR2                OUT
 SUB_OBJECT_NAME                VARCHAR2                OUT

Dentre algumas das listadas acima, temos:

• ABORT([sessionID]) - Finaliza a operação recuperável definida em uma sessão específica.
• GET_SESSION_TIMEOUT([sessionID]) - Retorna o tempo de timeout (em segundos) atualmente em vigor em uma sessão específica. Será retornado -1 se a sessão não existir.
• SET_SESSION_TIMEOUT([sessionID],[timeout]) - Seta imediatamente o tempo de timeout (em segundos) para uma sessão específica.
• GET_TIMEOUT() - Retorna o tempo de timeout (em segundos) definida na sessão corrente.
• SET_TIMEOUT([timeout]) - Seta imediatamente o tempo de timeout (em segundos) na sessão corrente.

Irei agora demonstrar através de eventos seqüenciais (1,2,3,4 e 5) o uso de uma dessas funções, mas para isso, o usuário SCOTT deverá ter o privilégio de execução no pacote DBMS_RESUMABLE conforme demonstrado abaixo:

SYS> grant execute on dbms_resumable to scott;

Concessão bem-sucedida.


Sessão Scott
------------

-- [evento 1]
SCOTT> alter session enable resumable;

Sessão alterada.

No evento acima, como o tempo timeout não foi especificado explicitamente, o valor padrão será de 2 horas.

-- [evento 2]
SCOTT> execute dbms_resumable.set_timeout(600);

Procedimento PL/SQL concluído com sucesso.

No evento 2 acima, resolvi alterar o valor de timeout para 600 segundos (10 minutos)

-- [evento 3]
SCOTT> insert into t1 select level from dual connect by level <= 100000;
[aguardando ...]

-- [evento 6]
insert into t1 select level from dual connect by level <= 100000
*
ERRO na linha 1:
ORA-01013: o usuário solicitou o cancelamento da operação atual

Após a execução do [evento 5], podemos ver acima que a operação de espera foi cancelada.

Sessão SYS
----------

-- [evento 4]
SYS> select dbms_resumable.get_session_timeout(29) timeout from dual;

   TIMEOUT
----------
       600

No evento acima estou verificando o timeout (em segundos) definido para a resolução do problema.

-- [evento 5]
SYS> exec dbms_resumable.abort(29);

Procedimento PL/SQL concluído com sucesso.

Para finalizar, no [evento 5] resolvi explicitamente cancelar a operação recuperável.