Introdução

Oracle Resumable Space Allocation é uma feature do Oracle Database que permite alterar o comportamento padrão quando uma sessão não consegue estender um segmento em um tablespace.

Normalmente esse cenário ocorre quando um usuário chega ao limite da sua cota no tablespace no qual ele está executando uma operação que requer alocação de espaço (ORA-01536)¹ ou quando o tablespace chega ao seu tamanho máximo, impossibilitando que qualquer usuário aloque espaço no mesmo (ORA-01652)².

¹ ORA-01536: space quota exceeded for tablespace <tablespace_name>

² ORA-01652: unable to extent temp segment in tablespace <tablespace_name>

Por padrão, quando qualquer um dessas situações ocorre em uma sessão, imediatamente o Oracle lança uma exceção para o usuário e aborta a operação.

Com Resumable Space Allocation ativado na instância ou na sessão do usuário, o Oracle não retorna uma exceção imediatamente para o aplicativo. No entanto, a operação será suspensa e ficará a espera de um ajuste do DBA para que a operação seja concluída com sucesso.

Habilitando o Resumable Space Allocation

A feature é habilitada alterando o parâmetro de inicialização “resumable_timeout” para qualquer valor maior que 0. O parâmetro é dinâmico e pode ser alterado a qualquer momento. O valor é definido em segundos.

Um exemplo de timeout com 10 minutos (600 segundos):

alter system set resumable_timeout = 600;

Com essa configuração, todas as transações que encontrarem algum erro na alocação de espaço em qualquer tablespace no banco de dados, aguardará 10 minutos para que uma ação corretiva seja realizada e a mesma possa prosseguir normalmente. Caso nenhuma ação corretiva seja realizada dentro do período especificado, a exceção será lançada para o usuário/aplicativo.

Detectando sessões suspensas pelo Resumable Space Allocation

Quando um evento de Resumable Space Allocation ocorre, esse evento é notificado tanto no alert.log da instância quanto nas views DBA_RESUMABLE e USER_RESUMABLE. Também, a sessão gera um “wait event” da classe “Configuration” que pode ser consultado nas views dinâmicas v$session e v$session_wait.

No alert.log, o Oracle grava o log de todas as etapas do evento Resumable Space Allocation. Por exemplo, ele gera um registro quando uma sessão entra em espera por uma correção, um registro quando uma correção é realizada e a sessão é continuada, um registro quando o timeout é excedido ou quando quando a operação é abortada.

Se um ajuste for realizado mas ainda assim não for o suficiente para a operação ser concluída, o Oracle tira a sessão do modo espera, tenta prosseguir a operação, encontra o mesmo erro e a sessão é suspensa novamente. Com isso, o arquivo alert.log terá entradas repetidas para o mesmo evento.

Testando na Prática

Para realizar os testes a seguir, criaremos um usuário e dois tablespaces de exemplo. Em um dos tablespaces, o usuário terá uma cota ilimitada, porém o tablespace terá um tamanho de 1MB. Por outro lado, o segundo tablespace terá um tamanho de 500MB, mas a cota do usuário será de 1MB. Essa configuração permitirá testar os dois cenários de erros com ORA-01536 e ORA-01652.

-- criando as tablespaces
create tablespace ts_teste1 datafile size 1m;
create tablespace ts_teste2 datafile size 500m;

--criando o usuario e definindo cota nas duas tablespaces
create user dibiei identified by dibiei account unlock
default tablespace ts_teste1
quota unlimited on ts_teste1
quota 1m on ts_teste2;

--grant necessarios
grant connect, resource, create table, select any table to dibiei;

Exemplo 1: Cenário com erro ORA-01652

Conectando no banco de dados com o usuário dibiei e criando uma tabela na tablespace ts_teste1 que requer uma alocação maior que 1MB:

create table tabteste1 
tablespace ts_teste1
as select * 
    from all_objects;

Consultando o alert.log

statement in resumable session 'User DIBIEI(107), Session 1037, Instance 1' was suspended due to

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

Consultando a view DBA_RESUMABLE

Enquanto isso, a sessão do usuário dibiei continua esperando:

Tentativa de correção 1: Adicionar um datafile de 10 MB, isso não será o suficiente.

alter tablespace ts_teste1 add datafile size 10m;

Novas entradas no alert.log

alter tablespace ts_teste1 add datafile size 10m
 Completed: alter tablespace ts_teste1 add datafile size 10m
 Sun Sep 29 16:46:42 2019
 statement in resumable session 'User DIBIEI(107), Session 1037, Instance 1' was resumed
 statement in resumable session 'User DIBIEI(107), Session 1037, Instance 1' was suspended due to
     ORA-01652: unable to extend temp segment by 128 in tablespace TS_TESTE1

Note que o tablespace foi alterado, o Oracle então tentou continuar a sessão (was resumed) e em seguida a operação foi suspensa com o mesmo erro novamente.

Tenativa de correção 2: Adicionando um datafile de 300MB, isso será o suficiente:

alter tablespace ts_teste1 add datafile size 300m;

Novas entradas no alert.log

alter tablespace ts_teste1 add datafile size 300m
 Completed: alter tablespace ts_teste1 add datafile size 300m
 Sun Sep 29 16:49:26 2019
 statement in resumable session 'User DIBIEI(107), Session 1037, Instance 1' was resumed

Dessa vez a sessão foi continuada e a tabela enfim foi criada. Note no print abaixo que o tempo de execução da instrução create table levou aproximadamente 569,58 segundos. Esse foi o tempo que a sessão ficou suspensa, mais o tempo que o Oracle precisou para criar a tabela após ter espaço disponível.

Consultando a view DBA_RESUMABLE novamente:

A view manterá o registro até o fim da sessão. Porém, agora ela informa o status “NORMAL”, isso indica que o timeout expirou. Independente se a correção foi aplicada ou se o usuário recebeu uma exceção após o tempo de 600 segundos, o status será “NORMAL”.

Isso quer dizer que a sessão não está em espera e também não houve intervenção do DBA ou do próprio usuário. Se houvesse, o status seria “ABORT”, pois o DBA ou o usuário proprietário da sessão pode abortar a operação a qualquer momento com a package DBMS_RESUMABLE.

Exemplo de como abortar uma sessão suspensa (assumindo que o id da sessão seja 1037):

execute dbms_resumable.abort(sessionid=>1037);

Exemplo 2: Cenário com erro ORA-01536

Criando uma nova tabela no tablespace ts_teste2, que tem um tamanho de 500 MB, suficiente para criar a tabela, mas o usuário tem uma cota limitada em 1MB.

create table tabteste2
tablespace ts_teste2
as select * 
    from all_objects;

Novas entradas no alert.log

statement in resumable session 'User DIBIEI(107), Session 1037, Instance 1' was suspended due to
     ORA-01536: space quota exceeded for tablespace 'TS_TESTE2'

Consultando a view DBA_RESUMABLE

select session_id, status, timeout, start_time, error_msg from dba_resumable;

Correção: Aumentar a cota do usuário para um valor satisfatório para a operação:

alter user dibiei quota 300m on ts_teste2;

Novas entradas no alert.log

Sun Sep 29 17:12:26 2019  statement in resumable session 'User DIBIEI(107), Session 1037, Instance 1' was resumed

Operação concluída com sucesso. Note que dessa vez o tempo total foi aproximadamente 317,18 segundos.

Ativando Resumable Space Allocation no nível da sessão

O recurso de Resumable Space Allocation também pode ser definido pelo usuário com o comando ALTER SESSION. Abaixo, alguns exemplos de opções possíveis:

O usuário logado precisa da permissão “resumable”.

grant resumable to dibiei;

Habilitando Resumable Space Allocation na sessão atual. Caso o parâmetro tenha sido alterado no nível da instância, o timeout será o mesmo do parâmetro de inicialização “resumable_timeout“. Caso contrário, o valor padrão é 7200 segundos (2 horas).

--habilitando Resumable Space Allocation na sessão com timeout padrão 
--parâmetro resumable_timout ou 7200 segundos
alter session enable resumable;

--desablitando Resumable Space Allocation na sessão
alter session disable resumable;

Habilitando Resumable Space Allocation na sessão atual, definindo um valor de timeout de 5 minutos (300 segundos):

alter session enable resumable timeout 300;

A configuração no nível da sessão também permite especificar um nome, o qual será gravado no alert.log e na coluna “NAME” nas views DBA_RESUMABLE e USER_RESUMABLE:

alter session enable resumable timeout 300 name 'teste-dibiei';

Verificando o nome (primeira coluna) na view DBA_RESUMABLE

Verificando a entrada nomeada no alert.log

statement in resumable session 'teste-dibiei' was suspended due to
     ORA-01536: space quota exceeded for tablespace 'TS_TESTE2'

Este post apresentou dois cenários em que a feature Resumable Space Allocation se aplica e exemplos práticos para entender o seu funcionamento. Contudo, as possibilidades de uso não se limitam aos exemplos demonstrados aqui.

A Oracle tem uma documentação completa com informações sobre onde você pode utilizar Resumable Space Allocation e mais configuações avançadas em “Database Administrator’s Guide“, na seção “Managing Resumable Space Allocation“.