13.5. Leveraging Resumable Space Allocation

Until Resumable Space Allocation was introduced in Oracle 9i, you had to implement workarounds to prevent a long-running operation from failing from a space-related error condition, for example, artificially splitting up a job into smaller pieces so that a failure of one piece of the job would not require you to rerun the earlier parts of the job. Using Resumable Space Allocation, your developers can design their applications from a logical transaction point of view and give you an opportunity to fix the out-of-space condition while the application's long-running operation is temporarily suspended.

In the following sections, we will give you an overview of the Resumable Space Allocation architecture, show you how to use Resumable Space Allocation, and show you both the SQL and PL/SQL interfaces for Resumable Space Allocation. Finally, we will review the data dictionary views related to Resumable Space Allocation.

13.5.1. An Overview of Resumable Space Allocation

Resumable Space Allocation allows you to suspend and resume execution of large database operations that fail due to errors related to space limits and out-of-space conditions. Once you repair the condition by either adjusting quotas, or adding disk space to a tablespace, or both, the suspended operation automatically resumes where it left off.

When a session is enabled for Resumable Space Allocation, the long-running operation is suspended when one of the following occurs:

• The tablespace used by the operation runs out of space.

• The maximum number of extents in a table, index, temporary segment, rollback segment, cluster, LOB, table partition, or index partition has been reached, and no further extents can be allocated.

• The quota for the user executing the long-running operation has been reached.

When a statement is suspended under Resumable Space Allocation, the error is reported in the alert log and any triggers registered on the resumable system event are executed.

The types of SQL statements and operations that can trigger a Resumable Space Allocation condition include the following:

• SQL queries that run out of temporary space, such as for sorting

• DML commands such as INSERT, UPDATE, and DELETE

• Import/export operations, when Resumable Space Allocation is specified

• SQL*Loader jobs, when Resumable Space Allocation is specified

• DDL operations such as CREATE TABLE, CREATE INDEX, ALTER TABLE, ALTER INDEX, and CREATE MATERIALIZED VIEW

Also, a timeout parameter is associated with each Resumable Space Allocation suspension. If the space condition is not corrected within the timeout period, the transaction in progress is cancelled and rolled back as if Resumable Space Allocation was not enabled.

13.5.2. Using Resumable Space Allocation

Resumable Space Allocation is enabled via a new option of the ALTER SESSION command:

ALTER SESSION ENABLE RESUMABLE
     [TIMEOUT timeout] [NAME name];

The value for timeout is specified in seconds; if it is not specified, it defaults to the value stored in the initialization parameter RESUMABLE_TIMEOUT. You can specify a name for the resumable session; if it is not specified, a system-assigned name is used. In the following example, you enable Resumable Space Allocation in your session and set the timeout to 10 minutes (600 seconds):

SQL> alter session enable resumable timeout 600 name 'Short Timeout';
Session altered.

If any space-related condition occurs in this session that is not resolved within 10 minutes, the transaction is cancelled and rolled back. The value for NAME is stored in the data dictionary views USER_RESUMABLE and DBA_RESUMABLE.

Disabling Resumable Space Allocation in a session uses a different option of the ALTER SESSION command:

SQL> alter session disable resumable;
Session altered.

For a user to use Resumable Space Allocation, the user must be granted the RESUMABLE system privilege, as in this example:

SQL> grant resumable to scott;
Grant succeeded.

13.5.3. DBMS_RESUMABLE Package

The PL/SQL package DBMS_RESUMABLE gives you more control over sessions running in Resum-able Space Allocation mode. The procedures within DBMS_RESUMABLE are as follows:

ABORT Cancels a suspended Resumable Space Allocation transaction.

GET_SESSION_TIMEOUT Returns the timeout value for a given session.

GET_TIMEOUT Returns the timeout value for the current session.

SET_SESSION_TIMEOUT Sets the timeout value for a given session.

SET_TIMEOUT Sets the timeout value for the current session.

SPACE_ERROR_INFO Returns an error code for a particular object if it is currently triggering Resumable Space Allocation.

In the following example, you retrieve the timeout value for your session and double it:

SQL> select dbms_resumable.get_timeout() from dual;

DBMS_RESUMABLE.GET_TIMEOUT()
----------------------------
                        600

1 row selected.

SQL> exec dbms_resumable.set_timeout(1200);

PL/SQL procedure successfully completed.

SQL> select dbms_resumable.get_timeout() from dual;

DBMS_RESUMABLE.GET_TIMEOUT()
----------------------------
                       1200

1 row selected.

13.5.4. Using the AFTER SUSPEND System Event

A system event called AFTER SUSPEND, first introduced in Oracle 9i, is triggered at the database or schema level when a correctable Resumable Space Allocation error occurs. The trigger defined on this system event can take a number of corrective actions such as adjusting the timeout value and sending an e-mail to the DBA requesting assistance while the session is suspended. In the following example, you create a trigger to set the timeout to eight hours and send an e-mail to the DBA:

CREATE OR REPLACE TRIGGER resumable_default_timeout
   AFTER SUSPEND ON DATABASE
BEGIN
   /* set timeout to 8 hours */
   DBMS_RESUMABLE.SET_TIMEOUT(28800);
   /* prepare e-mail to DBA on call */
   . . .
   UTL_MAIL.SEND (. . .);
END;

13.5.5. Resumable Space Allocation Data Dictionary Views

The data dictionary views DBA_RESUMABLE and USER_RESUMABLE display the set of Resumable Space Allocation statements in the system. The view contains all currently executing or suspended statements that are running in Resumable Space Allocation mode. In the following query against DBA_RESUMABLE, you can review the user number, session number, name, status, timeout value, and the current SQL statement for all sessions that have enabled Resumable Space Allocation:

SQL> select user_id, session_id, name, status,
  2      timeout, sql_text
  3  from dba_resumable;

USER_ID SESSION_ID NAME STATUS TIMEOUT SQL_TEXT
-------- ----------- -------- ------ ------- ------------
      58         258 Short Ti NORMAL     600 select dbms_
                     meout                   resumable.ge
                                              t_timeout()
                                               from dual

64          239 Short Ti NORMAL   1200 select user_
                      meout                  id, session_
                                             id, name, st
                                             atus, timeou
                                              t, sql_text
                                             from dba_res
                                                   umable
                                          2 rows selected.