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=; ########set resumable_timeout value  seconds

Session altered.

SQL>
           

end