Como reasumir procesos que cancelan por falta de espacio (Resumable Space Management)

A partir de Oracle 9i se introduce el mecanismo para suspender y luego reasumir procesos que se quedan sin espacio disponible en el tablespace o alcanzan limitaciones de quota. Este feature permite al operador o dba ejecutar tareas correctivas y así evitar la generación de errores por falta de espacio.
Luego de que la condición de error es corregida la operación suspendida se reasume automáticamente Como funciona la resumisión de espacio alocado

Una sentencia esta habilitada para reasumir si la sesión desde donde fue ejecutada cumple alguna de las siguientes condiciones:

• El parámetro de inicio RESUMABLE_TIMEOUT es distinto a 0.
• Se habilita la sesión para resumir mediante: ALTER SESSION ENABLE RESUMABLE.

Una sentencia con resumisión habilitada es suspendida cuando ocurre una de las siguientes condiciones:

• Falta de espacio.
• Cantidad máxima de extents alcanzada.
• Quota de espacio excedida.

Cuando una sentencia es suspendida se generan las siguientes acciones:

• Se reporta el error en Alert log.
• El sistema genera una alerta de sesión reasumible suspendida.
• Si existe algún trigger registrado que se dispare ante el evento de sistema “AFTER SUSPEND” se ejecuta.

La suspensión de la sentencia resulta en la suspensión de la transacción por lo tanto los recursos transaccionales se mantendrán “tomados” hasta que se reasuma.
Cuando se resuelve la condición de error (por ejemplo, por intervención del usuario o por que otra sentencia haya liberado espacio) la sesión suspendida reasume
automáticamente y se limpia la alerta de sesión resumible suspendida.
Una sentencia suspendida puede ser forzada a terminar mediante la ejecución de DBMS_RESUMABLE.ABORT().
Cada sentencia reasumible tiene asociado un time-out (el default es de 2 horas) que una vez superado retoma la excepción suspendida y retorna el error al usuario.
Una sentencia pude ser suspendida y reasumida multiples veces durante su ejecución.

Las siguientes operaciones pueden ser reasumidas:
Consultas: Las sentencias SELECT que requieran de espacio temporal para ordenar o agrupar.
DML: Las operaciones de INSERT, UPDATE o DELETE ejecutadas desde cualquier interface (OCI, SQLJ, PL/SQL).
Utilidades de Carga y Descarga de datos: Las utilidades como exp/imp, expdp/impdp y sql loader pueden ser parametrizadas por consola para reasumir.
DDL: Las siguientes sentencias son candidatas a reasumir:

CREATE TABLE ... AS SELECT
CREATE INDEX
ALTER INDEX ... REBUILD
ALTER TABLE ... MOVE PARTITION
ALTER TABLE ... SPLIT PARTITION
ALTER INDEX ... REBUILD PARTITION
ALTER INDEX ... SPLIT PARTITION
CREATE MATERIALIZED VIEW
CREATE MATERIALIZED VIEW LOG


Existen 3 tipos de errores que pueden ser corregidos usando resumisión:

Falta de espacio disponible
La operación no puede alocar un nuevo extent para una tabla/índice/undo/temporal/cluster/LOB/partición de una tabla/partición de un índice en un tablespace. Los siguientes errores son ejemplos del tipo de error por falta de espacio:
ORA-1653 unable to extend table ... in tablespace ...
ORA-1654 unable to extend index ... in tablespace ...

Máxima cantidad de extents
El número de extents maximo es alcanzado para una tabla/índice/undo/temporal/cluster/LOB/partición de una tabla/partición de un índice. Ejemplos de errores que entran en esta categoría son:
ORA-1631 max # extents ... reached in table ...
ORA-1654 max # extents ... reached in index ...

Cuota de espacio excedida
El usuario excedió el espacio disponible en un tablespace dado. El siguiente error es arrojado en dicho caso:

ORA-1536 space quote exceeded for tablespace …

Para reasumir una operación es necesario que la sesión se encuentre en modo de resumisión. La habilitación de dicho modo se puede realizar a nivel general configurando adecuadamente el parámetro de entorno RESUMABLE_TIMEOUT o a nivel sesión usando las cláusula ALTER SESSION. Dado que este tipo de sesiones
bloquean los objetos involucrados cuando entra en modo suspendido, es requisito que el usuario tenga el privilegio de sistema RESUMABLE.

Seteando el parámetro RESUMABLE_TIMEOUT a nivel global o de instancia todas las sesiones ejecutaran sentencias en modo reasumible. El valor default del parámetro es 0 lo cual implica que ninguna sesión permite reasumir. Por ejemplo:
ALTER SYSTEM SET RESUMABLE_TIMEOUT=3600
Implica que ante un evento de error como los citados anteriormente, la sesión quedará suspendida por un tiempo máximo de 1 hora

Utilización de ALTER SESSION para habilitar resumisión

Un usuario puede ejecutar:

Para Habilitar la resumisión:
ALTER SESSION ENABLE RESUMABLE

Para Deshabilitar la resumisión:
ALTER SESSION DISABLE RESUMABLE

También se puede definir el intervalo de time-out (7200 segundos si no se especifica)
ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600
También se puede nombrar a la sentencia para identificarla mas fácilmente:
ALTER SESSION ENABLE RESUMABLE TIMEOUT 1800 NAME 'insert into table';

Las siguientes vistas pueden ser usadas para monitorear el status de las sentencias con resumisión activada:

USER_RESUMABLE: Esta vista contiene detalle de todas las sentencias en ejecución o suspendidas en sesiones con resumisión habilitada de un determinado usuario.

DBA_RESUMABLE: Idem anterior pero para las sesiones de todos los usuarios

V$SESSION_WAIT: Cuando la sentencia queda suspendida la sesión invocante se pone en estado de wait y se puede ver un nueva fila en la vista con el siguiente evento “statement suspended, wait error to be cleared".


A fin de mostrar el uso de este feature se va a realizar la siguiente simulación:
Se crea un tablespace de 5Mb:

rop@TEST10G> create tablespace tbs datafile '/disco02/oracle10g/oradata/TEST10G/tbs01.dbf' size 5m
Tablespace creado.

Luego para una tabla T con un solo campo CHAR(100) insertamos filas hasta que se produce el error:

rop@TEST10G> create table t (x char(100)) tablespace tbs;
                                   Tabla creada.
                                   rop@TEST10G> insert into t select 'a' from dual connect by rownum <= 100000; insert into t select 'a' from dual connect by rownum <= 100000             * ERROR en línea 1: ORA-01653: no se ha podido ampliar la tabla ROP.T con 128 en el tablespace TBS Ahora habilitamos la misma sesión para que suspenda y no genere error: rop@TEST10G> alter session enable resumable;
                                   Sesión modificada.
                                   rop@TEST10G> insert into t select 'a' from dual connect by rownum <= 100000; La sesión queda suspendida. Si realizamos la consulta en USER_RESUMABLE se ve lo siguiente:  USER_ID    77 SESSION_ID    65 INSTANCE_ID    1 COORD_INSTANCE_ID    COORD_SESSION_ID    STATUS    SUSPENDED TIMEOUT    7200 START_TIME    06/06/08 15:59:32 SUSPEND_TIME    06/06/08 15:59:32 RESUME_TIME    NAME    User ROP(77), Session 65, Instance 1 SQL_TEXT    insert into t select 'a' from dual connect by rownum <= 100000 ERROR_NUMBER    1653 ERROR_PARAMETER1    ROP ERROR_PARAMETER2    T ERROR_PARAMETER3    128 ERROR_PARAMETER4    TBS ERROR_PARAMETER5    ERROR_MSG    ORA-01653: no se ha podido ampliar la tabla ROP.T con 128 en el tablespace TBS ORA-01653: no se ha podido ampliar la tabla ROP.T con 128 en el tablespace TBS 

Luego desde otra sesión se agrega espacio:

rop@TEST10G> alter database datafile '/disco02/oracle10g/oradata/TEST10G/tbs01.dbf' resize 200m;

y una vez agregado el espacio adicional la otra sesión continua insertando las filas que faltaban:

rop@TEST10G> insert into t select 'a' from dual connect by rownum <= 100000;                                         100000 filas creadas. 

Con este mecanismo se evita que los errores por falta de espacio ocasionen tener que reprocesar todo nuevamente. Cuando una sesión se queda sin espacio suficiente se mantiene suspendida hasta que se agrega mas espacio y luego en forma automática continúa su ejecución hasta que finaliza.
Como contrapartida ese mecanismo debe ser utilizado con cierto cuidado ya que las sesiones suspendidas dejaran transacciones sin confirmar y por lo tanto bloquearan a los objetos involucrados.
Para mas información: Managing Resumable Space Allocation.