Oracle Resumable Space Allocation

Oracle Tips by Burleson Consulting

[@more@]

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

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’s 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.

Using the dbms_resumable package

Oracle resumable space allocation is governed by the dbms_resumable package.

To use resumable space management must you enable it on a session-by-session basis using the alter session enable resumable command. By default if the space condition is not corrected after 2 hours, then the transaction will fail. You can configure a larger or smaller value if your needs require. Once enabled, Oracle will automatically detect the space condition and suspend the session. Oracle will write an entry to the alert log that the session has been suspended. Additionally the DBA_RESUMABLE view maintains a record of all currently suspended sessions. Once the DBA has corrected the space problem the suspended session will automatically resume its operation at the point of suspension.

Oracle also provides an after suspend system trigger event, that allows you to automate your response to a session suspend condition. Further the dbms_resumable package is provided to allow for management of resumable space management from within SQL or PL/SQL.

Inside resumable space allocation

Noted author Kirti Deshpande has some great notes on Oracle resumable space allocation, and this is an excerpt from one of his articles which discusses the internals of Oracle resumable space management:

Transactions and jobs fail for a number of reasons, e.g., application errors, data errors, hardware errors etc. In this paper, we will focus on failures due to disk space errors.

The following three categories broadly address such disk space errors that cause the jobs to fail in an Oracle database environment:

How to Use Resumable Space Allocation

Enable/Disable Resumable Space Allocation in Oracle9i Database and Oracle Database10g

Grant the use ALTER SESSION and RESUMABLE privilege to use this feature. The user may also need EXECUTE privilege on a new package called DBMS_RESUMABLE to carry out certain steps (described later in the paper)

After establishing the session, the user issues the following command:

ALTER SESSION ENABLE RESUMABLE [TIMEOUT n] [NAME ’string’];

Enable/Disable Resumable Space Allocation in Oracle Database 10g

In Oracle Database 10g, you have a choice of either using the same oracle9i Database procedure to enable/disable resumable mode for sessions or letting all sessions run in resumable mode by setting a new initialization parameter. Oracle Database 10g introduces RESUMABLE_TIMEOUT parameter.

It defaults to a value 0, meaning no sessions will be resumable mode by default. However, you can change the value of this dynamic parameter to allow future sessions to run in resumable mode using the timeout value set for this global parameter. There is no need to grant ALTER SESSION and RESUMABLE privilege to the users. You can either set RESUMABLE_TIMEOUT=7200(for 2 hours of timeout)in the init.ora file and bounce the database, or simply issue one of the following commands to enable resumable operations for all new sessions.

ALTER SYSTEM SET RESUMABLE_TIMEOUT=7200:

ALTER SYATEM SET RESUMABLE_TIMEOUT=0 SCOPE=BOTH; (if using SPFILE)

Mon Feb 19 12:36;46 2007

statement in reumable session ‘user WEBUSER(106),Session 21,Instance1’was suspended due to ORA-01536: space quota exceeded for tablespace ’USERS”

v$Session-Wait view

As mentioned earlier the suspended session is actually waiting on an event.You can track all such sessions from V$SESSION-WAIT view using following query:

select sid, wait_time,
seconds-in-wait, state, event
from v$session-wait
where event like ’statement suspended%’;

Resumable Space Allocation in Oracle Utilities

Starting with Oracle9i Database, the utilities SQL*Loader, EXPORT and IMPORT have additional parameters to make use of resumable space allocation feature.

These utilities have three new parameters as described below:

1. RESUMABLE: For SQL* Loader if defaults to ’FALSE’ and for Export and Import it defaults to ‘ N’, meaning there is no resumable operation by default. Setting this parameter to ‘Y’(Export/Import) or ‘TRUE’ (for SQL*Loader) will trigger the utility to run in resumable space allocation mode. For SQL*Loader and Import the job will be suspended when it encounters any space error. For Export utility, the resumable operation applies to job running out of database disk space and not the file system uses the QUERY option with ORDER BY clause, then running the export with RESUMABLE=Y will suspend the job in case the sort of operation runs out of space in the temporary tablespace.

2. RESUAMBLE_TIMEOUT: Determines how long the job can stay suspended before correcting the space error. Expressed in seconds.

3. RESUMABLE_NAME: Assigns a user-defined name to the suspended job.

Limitations of Resumable Space Allocation

If you use Dictionary Managed tablespaces in your databases then there are a couple of limitations of resumable space allocation feature. Certain operations will not be resumable.

1. If you are creating a table or index specifying the storage clause with maxextents not to set to unlimitied, then this DDL operation will fail fit encounters space error even when run as a resuamble statement. The workaround is to specify unlimited for maxextents, or create the object in Locally Managed tablespace. If desired, you can change the value of maxextents after creating the object in the Dictionary Managed tablespace. This limitation only affects CREATE statements. Subsequent DML operations can be performed in resumable mode.

2 - The other limitation applies to rollback segments. If those are in a Dictionary Managed tablespace, then resumable space allocation does not work for statements encountering any space error related to rollback segments. To be able to use resumable space allocation feature in such cases, you must create the rollback segments in a Locally Managed tablespace, or use Automatic Undo Management feature, which by default uses Locally Managed tablespace for undo segments.

1. Unable to Extend Segment