DBA Tips Archive for Oracle


Resumable Space Management - (Oracle 9i)

by Jeff Hunter, Sr. Database Administrator


  1. Introduction
  2. Features and Limitations
  3. Candidate Database Operations for Resumable Space Management
  4. Using Resumable Space Allocation
  5. AFTER SUSPEND Trigger
  6. Data Dictionary Views Associated with Resumable Space Management
  7. Resumable Space Management and Oracle Utilities


One issue DBAs often run in to is that of running out of space during either long data loads or long running queries that exhaust all of the temporary tablespace for the database. Consider the frastration when attempting a large data load that has been running for 10 hours only to have it run out of space and rollback the entire transaction. Also consider a query that ran for 9 hours before it ran out of temporary tablespace during its final sort.

With the introduction of Resumable Space Management in Oracle9i, situations like this should be a thing of the past. This article will take a detailed look into resumable space management, when it can be used, and how to enable and disable this new feature. I will then review some of the various administration issues around resumable space management and provide examples of this new feature in action.

Features and Limitations

Resumable Space Allocation suspends a large operation in the event of a space allocation failure so the DBA can take corrective action. Execution will resume automatically once the action is corrected. An operation statment that is resumable space allocation enabled, is called a resumable statement. In the absence of resumable space allocation, the space allocation failure would have resulted in the operation being rolled back.

With Resumable Space Allocation enabled, the following conditions can be corrected:

When one of these conditions is reached, and if resumable space management has been enabled for the session, then the resumable statement will be suspended. At the time the statement is suspended, an error will be raised in the alert.log file. In addition, the user session running the query will become suspended, until either the time-out period passes, or the error condition is resolved.

Once a statement is suspended, it will wait for a defined period of time (the default being two hours / 7,200 seconds). After that period of time elapses, the error will be raised, and the statement rolled back. During the period of the statement suspension, if the condition that caused the statement to be suspended is corrected, then the statement will automatically resume execution. For example, if space in the temporary tablespace were released by another user session, the query that was suspended because it ran out of temporary tablespace space would resume automatically without user intervention.

Suspended operations can be monitored through the use of the DBA_RESUMABLE and USER_RESUMABLE views Also, Oracle provides a package called DBMS_RESUMABLE that allows you to manage the Resumable Space Management features of the database, which will be discussed later in this article.

Candidate Database Operations for Resumable Space Management

There are many different kinds of operations that can take advantage of Resumable Space Management. The following list are the operations that are resumable:

Using Resumable Space Allocation

Resumable space management is controlled on a session-by-session level, and is disabled by default. Any user who wants to enable resumable space management must first be granted the resumable system privilege. Having been granted that privilege, the user will enable resumable space management features by issuing the ALTER SESSION ENABLE RESUMABLE command. Likewise, to disable resumable space management, issue the command ALTER SESSION DISABLE RESUMABLE. If you wish to cause specific users to enable resumable space management, then create a login trigger to alter the users' sessions when they log in to the database.

You can set the session in the resumable mode with the following statement:

You will need the RESUMABLE system privilege to perform this operation. You can set this privilege as follows:
You can disable resumable mode using the following statement:
You can use the following statement to specify a timeout (in seconds) after which the suspended operation will be rolled back and return an error:
You can also specify the timeout as the standard for the suspend operation as follows. This should be run as the SYS user:

You can specify the timeout interval for resumable statements in a session with a DBMS_RESUMABLE.SET_SESSION_TIMEOUT procedure call. When you are done, you can also name the operation as follows:
You can abort the resumable statement with the DBMS_RESUMABLE.ABORT procedure. You can also get information about the error by invoking the DBMS_RESUMABLE.SPACE_ERROR_INFO procedure or by querying the USER_RESUMABLE or DBA_RESUMABLE views.


Each time a SQL statement is suspended because of a space management failure, Oracle will fire an AFTER SUSPEND event trigger, if one exists. This trigger can be used for a number of reasons, including controlling which type of space allocation failures you wish to suspend, and which you wish to allow. You can also use the trigger to call custom code for notification purposes, such as sending e-mail to notify the DBA or a monitoring group that the process had failed.

You can specify a trigger that contains custom code at either the database or schema level to resond to the AFTER SUSPEND system event. Keep in mind that the SQL code within the AFTER SUSPEND trigger is autonomous and it is not itself resumable. Here is code that can be used to specify an AFTER SUSPEND trigger:


     | You would perform your transaction declaration here.
     | This declaration will be an autonomous transaction
     | since it is within a trigger.


     | Your custom code would go here.
     | Whenever the operation is suspended, this code will be invoked.



Data Dictionary Views Associated with Resumable Space Management

The principle views associated with resumable space management are the DBA_RESUMABLE and USER_RESUMABLE views. These views contain information on each statement that is currently suspended. Here is an example of a query against the DBA_RESUMABLE view:
  SQL> SELECT user_id, session_id, error_msg FROM dba_resumable;

  ------- ---------- ---------------------------------------------
       18       1385 ORA-30036: unable to extend segment by 128 in
                     undo tablespace 'USERS'
In this example, session 18 has a suspended session. It is apparently stalled, waiting for space in a data tablespace called USERS. At this point, a DBA would have a few options to resume this statement. The DBA could add space to the USERS tablespace or enable AUTOEXTEND. Of course, once Oracle (or the DBA) has resolved the problem, it will automatically restart the suspended session.

Another view that has some use in conjunction with resumable space management is the V$SESSION_WAIT view. In this view, there will be an event for each statement that is suspended. The name of the event is Suspended on space error. Here is a query that displays this event:

  SQL> SELECT sid, event, seconds_in_wait
    2  FROM v$session_wait WHERE sid = 18;

  SID EVENT                                         SECONDS_IN_WAIT
  --- --------------------------------------------- ----------------
   18 statement suspended, wait error to be cleared 648
Also, the V$SYSTEM_EVENT and V$SESSION_EVENT views provide wait information on suspension events. Here are some example queries from those tables of a session waiting on a suspended session:
  SQL> SELECT event, total_waits, time_waited 
    2  FROM v$system_event WHERE event like '%suspend%';

  EVENT                           TOTAL_WAITS TIME_WAITED
  ------------------------------- ----------- -----------
  statement suspended, wait error         68       12512
  to be cleared

  SQL> select sid, event, total_waits, time_waited 
    2  FROM v$session_event WHERE event like '%suspend%';

  SID EVENT                           TOTAL_WAITS TIME_WAITED
  --- ------------------------------- ----------- -----------
   18 statement suspended, wait error          81       19029
      to be cleared 

Resumable Space Management and Oracle Utilities

In this section of the document, I will explore the use of reusable space management in concert with Oracle utility programs. First, I'll look at how this feature works with Oracle's Import and Export facilities. Then we will take a look at resumable space management and SQL*Loader.

Using imp and exp with Resumable Space Management

To facilitate the use of resumable space management in Oracle9i with Oracle's Import and Export utilities, new parameters have been added to the imp command. The new parameters include:

  • resumable
  • resumable_name
  • resumable_timeout

These parameters enable resumable space management when using the Import (imp) utility. As with an Oracle user session, if the imp session is suspended, then the imp process will freeze until the suspension time-out passes (in which case, the process will fail). Of course, if the space failure is fixed during the suspension, then the imp process will simply continue to process the work until it's finished or it's suspended again. The default is to not have resumable space features enabled with the imp facility.

Using SQL*Loader with Resumable Space Management

Oracle's SQL*Loader product has had the following command-line parameters added:
  • resumable
  • resumable_name
  • resumable_timeout
These parameters enable resumable space management features when loading data using SQL*Loader. The default is to not have resumable space features enabled.

Copyright (c) 1998-2018 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Thursday, 10-Aug-2006 20:23:53 EDT
Page Count: 53548