Oracle Resumable Space Allocation Oracle Database Tips by Donald Burleson

 

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 = <value in seconds>;
(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:

1.    Unable to Extend Segment

 When the job cannot create or allocate a new extent for the segment in question, the job aborts and Oracle reports an error stating so. The Oracle error message will state the name of the segment and its tablespace name. Oracle errors ORA-1650 to ORA-1655 fall in this category.

2. Maximum Extents Reached

When the number of extents for the segment in question reaches the maximum value set by its maxextents parameter, the job aborts. The Oracle message will state the maximum number of extents along with the segment name. Oracle errors ORA-1629 to ORA-1632 fall in this category.

3. Space Quota Exceeded

 When the user exceeds the assigned space quota for the segment's tablespace then the job aborts and Oracle reports following error:

     ORA-01536-'space quota exceeded for tablespace ?o/oS???

In the past several versions, oracle Corporation introduced a number of new techniques and removed some restrictions to allow objects to grow. You can minimize ?unable to extend? error using auto-extensible data files or you can set maxetents to ?unlimited? to avoid ?max# extents  reached? error. However, not everyone may implemental these features in all databases one supports for one reason or another. Reviewing and adjusting storage clause for all segments and datafiles to minimize or avoid out of disk space errors is a daunting task. Therefore, there will be incidents of jobs failing due to disk space errors. This is when the Resumable Space Allocation feature can be of immense help.

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 2015

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.

If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.