|Oracle® Database Administrator's Guide
11g Release 2 (11.2)
Part Number E17120-05
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.
This section contains the following topics:
This section provides an overview of resumable space allocation. It describes how resumable space allocation works, and specifically defines qualifying statements and error conditions.
The following is an overview of how resumable space allocation works. Details are contained in later sections.
A statement executes in a resumable mode only if its session has been enabled for resumable space allocation by one of the following actions:
A resumable statement is suspended when one of the following conditions occur (these conditions result in corresponding errors being signalled for non-resumable statements):
Out of space condition
Maximum extents reached condition
Space quota exceeded condition.
When the execution of a resumable statement is suspended, there are mechanisms to perform user supplied operations, log errors, and to query the status of the statement execution. When a resumable statement is suspended the following actions are taken:
The error is reported in the alert log.
The system issues the Resumable Session Suspended alert.
If the user registered a trigger on the
AFTER SUSPEND system event, the user trigger is executed. A user supplied PL/SQL procedure can access the error message data using the
DBMS_RESUMABLE package and the
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 is resolved (for example, as a result of user 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 throw the exception using the
DBMS_RESUMABLE.ABORT() procedure. This procedure can be called by a DBA, or by the user who issued the statement.
A suspension time out interval is associated with resumable statements. A resumable statement that is suspended for the timeout interval (the default is two hours) wakes up and returns the exception to the user.
A resumable statement can be suspended and resumed multiple times during execution.
The following operations are resumable:
SELECT statements that run out of temporary space (for sort areas) are candidates for resumable execution. When using OCI, the calls
OCIStmtFetch() are candidates.
DELETE statements are candidates. The interface used to execute them does not matter; it can be OCI, SQLJ, PL/SQL, or another interface. Also,
INSERT INTO...SELECT from external tables can be resumable.
As for SQL*Loader, a command line parameter controls whether statements are resumable after recoverable errors.
The following statements are candidates for resumable execution:
There are three classes of correctable errors:
Out of space condition
The operation cannot acquire any more extents for a table/index/temporary segment/undo segment/cluster/LOB/table partition/index partition in a tablespace. For example, the following errors fall in this category:
ORA-01653 unable to extend table ... in tablespace ... ORA-01654 unable to extend index ... in tablespace ...
Maximum extents reached condition
The number of extents in a table/index/temporary segment/undo segment/cluster/LOB/table partition/index partition equals the maximum extents defined on the object. For example, the following errors fall in this category:
ORA-01631 max # extents ... reached in table ... ORA-01632 max # extents ... reached in index ...
Space quota exceeded condition
The user has exceeded his assigned space quota in the tablespace. Specifically, this is noted by the following error:
ORA-01536 space quote exceeded for tablespace string
In a distributed environment, if a user enables or disables resumable space allocation, or if you, as a DBA, alter the
RESUMABLE_TIMEOUT initialization parameter, only the local instance is affected. In a distributed transaction, sessions or remote instances are suspended only if
RESUMABLE has been enabled in the remote instance.
In parallel execution, if one of the parallel execution server processes encounters a correctable error, that server process suspends its execution. Other parallel execution server processes will continue executing their respective tasks, until either they encounter an error or are blocked (directly or indirectly) by the suspended server process. When the correctable error is resolved, the suspended process resumes execution and the parallel operation continues execution. If the suspended operation is terminated, the parallel operation aborts, throwing the error to the user.
Different parallel execution server processes may encounter one or more correctable errors. This may result in firing an
AFTER SUSPEND trigger multiple times, in parallel. Also, if a parallel execution server process encounters a non-correctable error while another parallel execution server process is suspended, the suspended statement is immediately aborted.
For parallel execution, every parallel execution coordinator and server process has its own entry in the
Resumable space allocation is only possible when statements are executed within a session that has resumable mode enabled. There are two means of enabling and disabling resumable space allocation. You can control it at the system level with the
RESUMABLE_TIMEOUT initialization parameter, or users can enable it at the session level using clauses of the
ALTER SESSION statement.
Note:Because suspended statements can hold up some system resources, users must be granted the
RESUMABLEsystem privilege before they are allowed to enable resumable space allocation and execute resumable statements.
You can enable resumable space allocation system wide 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.
A user can enable resumable mode for a session, using the following SQL statement:
ALTER SESSION ENABLE RESUMABLE;
To disable resumable mode, a user issues the following statement:
ALTER SESSION DISABLE RESUMABLE;
The default for a new session is resumable mode disabled, unless the
RESUMABLE_TIMEOUT initialization parameter is set to a nonzero value.
The user can also specify a timeout interval, and can provide a name used to identify a resumable statement. These are discussed separately in following sections.
A timeout period, after which a suspended statement will error if no intervention has taken place, can be specified when resumable mode is enabled. The following statement specifies that resumable transactions will time out and error after 3600 seconds:
ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600;
The value of
TIMEOUT remains in effect until it is changed by another
ALTER SESSION ENABLE RESUMABLE statement, it is changed by another means, or the session ends. The default timeout interval when using the
ENABLE RESUMABLE TIMEOUT clause to enable resumable mode is 7200 seconds.
See Also:"Setting the RESUMABLE_TIMEOUT Initialization Parameter" for other methods of changing the timeout interval for resumable space allocation
Resumable statements can be identified by name. The following statement assigns a name to resumable statements:
ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600 NAME 'insert into table';
NAME value remains in effect until it is changed by another
ALTER SESSION ENABLE RESUMABLE statement, or the session ends. The default value for
NAME is '
The name of the statement is used to identify the resumable statement in the
Another method of setting default resumable mode, other than setting the
RESUMABLE_TIMEOUT initialization parameter, is that you can register a database level
LOGON trigger to alter a user's session to enable resumable and set a timeout interval.
Note:If there are multiple triggers registered that change default mode and timeout for resumable statements, the result will be unspecified because Oracle Database does not guarantee the order of trigger invocation.
When a resumable statement is suspended, the error is not raised to the client. In order for corrective action to be taken, Oracle Database provides alternative methods for notifying users of the error and for providing information about the circumstances.
When a resumable statement encounter 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 a
AFTER SUSPEND trigger are always non-resumable 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.
Triggers can also call the
DBMS_RESUMABLE package to terminate suspended statements and modify resumable timeout values. In the following example, the default system timeout is changed by creating a system wide
AFTER SUSPEND trigger that calls
DBMS_RESUMABLE to set the timeout to 3 hours:
CREATE OR REPLACE TRIGGER resumable_default_timeout AFTER SUSPEND ON DATABASE BEGIN DBMS_RESUMABLE.SET_TIMEOUT(10800); END; /
See Also:Oracle Database PL/SQL Language Reference for information about triggers and system events
The following views can be queried to obtain information about the status of resumable statements:
||These views contain rows for all currently executing or suspended resumable statements. They can be used by a DBA,
||When a statement is suspended the session invoking the statement is put into a wait state. A row is inserted into this view for the session with the
See Also:Oracle Database Reference for specific information about the columns contained in these views
DBMS_RESUMABLE package helps control resumable space allocation. The following procedures can be invoked:
||This procedure aborts a suspended resumable statement. The parameter
Oracle Database guarantees that the
The caller of
||This function returns the current timeout value of resumable space allocation for the session with
||This procedure sets the timeout interval of resumable space allocation for the session with
||This function returns the current
||This procedure sets a
See Also:Oracle Database PL/SQL Packages and Types Reference for details about the
When a resumable session is suspended, an operation-suspended alert is issued on the object that needs allocation of resource for the operation to complete. Once the resource is allocated and the operation completes, the operation-suspended alert is cleared. Please refer to "Managing Tablespace Alerts" for more information on system-generated alerts.
In the following example, a system wide
AFTER SUSPEND trigger is created and registered as user
SYS at the database level. Whenever a resumable statement is suspended in any session, this trigger can have either of two effects:
If an undo segment has reached its space limit, then a message is sent to the DBA and the statement is aborted.
If any other recoverable error has occurred, the timeout interval is reset to 8 hours.
Here are the statements for this example:
CREATE OR REPLACE TRIGGER resumable_default AFTER SUSPEND ON DATABASE DECLARE /* declare transaction in this trigger is autonomous */ /* this is not required because transactions within a trigger are always autonomous */ PRAGMA AUTONOMOUS_TRANSACTION; cur_sid NUMBER; cur_inst NUMBER; errno NUMBER; err_type VARCHAR2; object_owner VARCHAR2; object_type VARCHAR2; table_space_name VARCHAR2; object_name VARCHAR2; sub_object_name VARCHAR2; error_txt VARCHAR2; msg_body VARCHAR2; ret_value BOOLEAN; mail_conn UTL_SMTP.CONNECTION; BEGIN -- Get session ID SELECT DISTINCT(SID) INTO cur_SID FROM V$MYSTAT; -- Get instance number cur_inst := userenv('instance'); -- Get space error information ret_value := DBMS_RESUMABLE.SPACE_ERROR_INFO(err_type,object_type,object_owner, table_space_name,object_name, sub_object_name); /* -- If the error is related to undo segments, log error, send email -- to DBA, and abort the statement. Otherwise, set timeout to 8 hours. -- -- sys.rbs_error is a table which is to be -- created by a DBA manually and defined as -- (sql_text VARCHAR2(1000), error_msg VARCHAR2(4000), -- suspend_time DATE) */ IF OBJECT_TYPE = 'UNDO SEGMENT' THEN /* LOG ERROR */ INSERT INTO sys.rbs_error ( SELECT SQL_TEXT, ERROR_MSG, SUSPEND_TIME FROM DBMS_RESUMABLE WHERE SESSION_ID = cur_sid AND INSTANCE_ID = cur_inst ); SELECT ERROR_MSG INTO error_txt FROM DBMS_RESUMABLE WHERE SESSION_ID = cur_sid and INSTANCE_ID = cur_inst; -- Send email to receipient via UTL_SMTP package msg_body:='Subject: Space Error Occurred Space limit reached for undo segment ' || object_name || on ' || TO_CHAR(SYSDATE, 'Month dd, YYYY, HH:MIam') || '. Error message was ' || error_txt; mail_conn := UTL_SMTP.OPEN_CONNECTION('localhost', 25); UTL_SMTP.HELO(mail_conn, 'localhost'); UTL_SMTP.MAIL(mail_conn, 'sender@localhost'); UTL_SMTP.RCPT(mail_conn, 'recipient@localhost'); UTL_SMTP.DATA(mail_conn, msg_body); UTL_SMTP.QUIT(mail_conn); -- Abort the statement DBMS_RESUMABLE.ABORT(cur_sid); ELSE -- Set timeout to 8 hours DBMS_RESUMABLE.SET_TIMEOUT(28800); END IF; /* commit autonomous transaction */ COMMIT; END; /