Oracle Resumable Space Allocation

blueice

　　---------------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>　　---------------------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 RESUMABLE_TIMEOUT=0;
　　Within a session, a user can issue the>

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

　　SQL>>　　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>　　Statements do not suspend for an unlimited amount of time. A timed interval can be specified in the> 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>
     
  
• 
  　　Messages are written to the alert log>
  

　　-----------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>>
　　Session>
　　SQL>>
　　Session>　　SQL>
　　end