---------------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 = <value in seconds>;
(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_resumable and 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