Tip#14 Resumable Space Allocation

In the event of space allocation failures, rather than returning the error to the user and stopping the operation, the transaction can be temporarily suspended and corrective action taken. After the error condition is corrected, the suspended operation automatically resumes. This feature is called resumable space allocation. The affected statements are called resumable statements.


When the execution of a resumable statement is suspended, the system issues a Resumable Session Suspended alert. A suspended operation is automatically aborted if the error condition is not fixed within the time-out period. By default, the time-out period is two hours. you can enable resumable space allocation either at the system level, by setting the RESUMABLE_TIMEOUT initialization parameter to a nonzero value, or at the session level, by issuing the ALTER SESSION ENABLE RESUMABLE statement. A resumable statement can be suspended and resumed multiple times during execution.


Which statements are resumable?


• DML statements, including INSERT INTO ... SELECT from external tables, are resumable.
• DDL statements, including CREATE TABLE ... AS SELECT, are resumable.
• SELECT statements that run out of temporary space are also candidates for resumable execution.

A resumable statement can be suspended under any of these conditions:

• Space quota exceeded (e.g. The user has exceeded his or her assigned space quota in the tablespace),
• Maximum extents reached (e.g. The number of extents in a table or index equals the number of maximum extents defined on the object.)
• Out of space (e.g. The operation cannot acquire any more extents for an index in a tablespace.)