Managing Resumable Space Allocation - Oracle Administration Workshop 2

Managing Resumable Space Allocation - Oracle Database 10g

A resumable statement:

• Enables you to suspend large operations instead of receiving an error
• Gives you a chance to fix the problem while the operation is suspended, rather than starting over
• Is suspended for the following conditions:

1. Out of space
2. Maximum extents reached
3. Space quota exceeded

The Oracle database provides a means for suspending, and later resuming, the execution of large
database operations in the event of space allocation failures. This enables you 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. This feature is called
“resumable space allocation.” The statements that are affected are called “resumable
statements.”
A statement executes in resumable mode only when the resumable statement feature has been
enabled for the system or session.
Suspending a statement automatically results in suspending the transaction. Thus all
transactional resources are held through the suspension and resuming of a SQL statement. 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.
A suspension time-out interval is associated with resumable statements. A resumable statement
that is suspended for the time-out interval (the default is 7,200 seconds (2 hours)) reactivates
itself and returns the exception to the user. A resumable statement can be suspended and
resumed multiple times during execution.

Using Resumable Space Allocation

• Queries, DML operations, and certain DDL operations can be resumed if they encounter an
  out-of-space error.
• A resumable statement can be issued through SQL, PL/SQL, SQL*Loader, or the Oracle Call
  Interface (OCI).
• To run a resumable statement, you must first enable resumable statements for your session.

Using After Suspend Trigger To Automatically Resolve User Error's

• First Create A Procedure as shown Below. 

• Then create the after suspend trigger as shown in the example below.

Example:

1. An INSERT statement encounters an error saying the table is full.
2. The INSERT statement is suspended, and no error is passed to client.
3. Optionally, an AFTER SUSPEND trigger is executed.
4. Optionally, the SQLERRROR exception is activated to abort the statement.
5. If the statement is not aborted and free space is successfully added to the table, the INSERT statement resumes execution.

Detecting a Suspended Statement

When a resumable statement is suspended, the error is not raised to the client. In order for
corrective action to be taken, the Oracle database provides alternative methods for notifying
users of the error and for providing information about the circumstances.

Possible Actions During Suspension

When a resumable statement encounters a correctable error, the system internally generates the
AFTER SUSPEND system event. Users can register triggers for this event at both the database
and schema level. If a user registers a trigger to handle this system event, the trigger is executed
after a SQL statement has been suspended. SQL statements executed within an AFTER
SUSPEND trigger are always nonresumable and are always autonomous. Transactions started
within the trigger use the SYSTEM rollback segment. These conditions are imposed to overcome
deadlocks and reduce the chance of the trigger experiencing the same error condition as the
statement.
Within the trigger code, you can use the USER_RESUMABLE or DBA_RESUMABLE views, or
the DBMS_RESUMABLE.SPACE_ERROR_INFO function to get information about the
resumable statements.

When a resumable statement is suspended:

• The session invoking the statement is put into a wait state. A row is inserted into
  V$SESSION_WAIT for the session with the EVENT column containing “statement
  suspended, wait error to be cleared”.
• An operation-suspended alert is issued on the object that needs addition resources for the
  suspended statement to complete.

Ending a Suspended Statement

When the error condition is resolved (for example, as a result of DBA intervention or perhaps
sort space released by other queries), the suspended statement automatically resumes execution
and the “resumable session suspended” alert is cleared.

A suspended statement can be forced to activate the SERVERERROR exception by using the
DBMS_RESUMABLE.ABORT() procedure. This procedure can be called by a DBA, or by the
user who issued the statement. If the suspension time-out interval associated with the resumable
statement is reached, the statement aborts automatically and an error is returned to the user.