Oracle Resumable Space Allocation

—————The following is quoted from Concept 10g

Oracle provides a means for suspending, and later resuming, the
execution of large database operations in the event of space
allocation failures. This enables an administrator to take corrective
action, instead of the Oracle database server returning an error to
the user. After the error condition is corrected, the suspended
operation automatically resumes.

A statement runs in a resumable mode only when the client explicitly
enables resumable semantics for the session using the ALTER SESSION
statement.

Resumable space allocation is suspended when one of the following
conditions occur:

Out of space condition

Maximum extents reached condition

Space quota exceeded condition

For nonresumable space allocation, these conditions result in errors
and the statement is rolled back.

Suspending a statement automatically results in suspending the
transaction. Thus all transactional resources are held through a
statement suspend and resume.

When the error condition disappears (for example, as a result of user
intervention or perhaps sort space released by other queries), the
suspended statement automatically resumes execution.

———————The following is quoted from http://www.dba-oracle.com/t_resumable_space_allocation.htm

Resumable space allocation, introduced in Oracle 9i, is for all
tablespaces at the session level. Database operations are suspended
when an out-of-space condition is encountered. These suspended
operations automatically resume when the error condition disappears.
In Oracle Database 10g, this can be enabled at the instance level.
Besides this improvement, automatic alert notification is sent when an
operation is suspended. This feature can be enabled by the SQL
command. ALTER SYSTEM SET RESUMABLE_TIMEOUT = ;
(Substitute 3600 for 1 hour)

Setting resumable_timeout Setting the resumable_timeout initialization
parameter, you can enable resumable space allocation system and
specify a timeout interval by setting the resumable_timeout
initialization parameter. For example, the following setting of the
resumable_timeout parameter in the initialization parameter file
causes all sessions to initially be enabled for resumable space
allocation and sets the timeout period to 1 hour: RESUMABLE_TIMEOUT =
3600 If this parameter is set to 0, then resumable space allocation is
disabled initially for all sessions. This is the default. You can use
the ALTER SYSTEM SET statement to change the value of this parameter
at the system level. For example, the following statement will disable
resumable space allocation for all sessions: ALTER SYSTEM SET
RESUMABLE_TIMEOUT=0; Within a session, a user can issue the ALTER
SESSION SET statement to set the resumable_timeout initialization
parameter and enable resumable space allocation, change a timeout
value, or to disable resumable mode. Using ALTER SESSION to enable and
disable Resumable Space Allocation, a user can enable resumable mode
for a session.

Alter session enable resumable ;
note:You may encouter the error as the following:

SQL> alter session set resumable_timeout=3600; ERROR: ORA-02097:
parameter cannot be modified because specified value is invalid
ORA-01031: insufficient privileges

SQL> conn /as sysdba Connected. SQL> grant resumable to keke;

Grant succeeded.

The ALTER SESSION ENABLE RESUMABLE statement is used to activate
resumable space allocation for a given session. Developers are able to
embed the ALTER SESSION statement in programs to activate resumable
space allocation. A new parameter, called RESUMABLE, is used to enable
resumable space allocation for export, import and load utilities.

Statements do not suspend for an unlimited amount of time. A timed
interval can be specified in the ALTER SESSION statement to designate
the amount of time that passes before the statement wakes up and
returns a hard return code to the user and rolls back the unit of
work. If no time interval is specified, the default time interval of
two hours is used.

When a resumable statement suspends because of an out of space
condition, the following actions occur:

A triggerable system event is initiated. Developers are able to code
triggers that fire when a statement suspends.

Entries are placed into system data dictionary tables. The data
dictionary views dba_resumableand user_resumable can be accessed to
retrieve the paused statement identifier, text, status and error
message.

Messages are written to the alert log identifying the statement and
the error that caused the statement to suspend.

———–So,If you want to use this feature in session level,you can do this:

SQL> grant resumable to keke;

Grant succeeded.

SQL> conn keke/oracle
Connected.

SQL> alter session enable resumable;######if you enable resumable,not set resumable_timeout,then the resumble_timeout default value is two hours

Session altered.
SQL> alter session set resumable_timeout=1800; ########set resumable_timeout value 1800 seconds

Session altered.

SQL>

end