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.

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

• Running out of tablespace space - Also known as an out of space error, happens when you cannot get any more extents for various database objects in a tablespace. This includes errors such as ORA-1650 (Unable to extend rollback segment), ORA-1653 (Unable to extend table), and ORA-1654 (Unable to extent index), in which Oracle is not able to allocate another extent due to lack of available space. Adding space to the given tablespace generally solves this error condition (though coalescing space might be an alternative). If this error involves a temporary tablespace, other user sessions can result in the release of temporary segments in the tablespace, thus freeing space for the suspended session.

• Maximum number of extents reached - You have a maximum extents reached error when the number of extents in various database objects exceeds the maximum extents that you have specified for the object. This includes errors such as ORA-1628 (Max # of extents reached for rollback segment), and ORA-1631 ORA-1654 (Max # of extents reached in Table or Index). In this case, the object has a MAX_EXTENTS value assigned to it, which has been reached. The solution to this problem is to increase the MAX_EXTENTS setting for the object in question.

• Attempt to exceed a tablespace quota - You will have a tablespace quota exceeded error when you exceed the space quota that was assigned to you in the tablespace. This includes error ORA-1536 (space quota exceeded for tablespace). The solution to correcting this problem is to increase the user quota for the tablespace in question.

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.

• SELECT queries - If the query runs out of temporary sort space, then it can be suspended and resumed.

• INSERT, UPDATE, DELETE operations - These operations can be suspended if they raise an out-of-space exception.

• INSERT ... AS SELECT operation - This operation can be suspended if it runs into an out-of-space condition.

• Import/Export operations - Resumable space management is supported by the IMP and EXP facilities of Oracle.

• SQL*Loader - Supports resumable space management operations.

• CREATE TABLE ... AS SELECT - Supports resumable space management.

• CREATE INDEX - Supports resumable space management.

• ALTER INDEX ... REBUILD - Supports resumable space management.

• ALTER TABLE ... MOVE PARTITION - Supports resumable space management.

• ALTER TABLE ... SPLIT PARTITION - Supports resumable space management.

• ALTER INDEX ... REBUILD PARTITION - Supports resumable space management.

• ALTER INDEX ... SPLIT PARTITION - Supports resumable space management.

• CREATE MATERIALIZED VIEW - Supports resumable space management.

• CREATE MATERIALIZED VIEW LOG - Supports resumable space management.

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

  SQL> ALTER SESSION ENABLE RESUMABLE;

  SQL> GRANT RESUMABLE TO scott;

  SQL> ALTER SESSION DISABLE RESUMABLE;

  SQL> ALTER SESSION ENABLE RESUMABLE TIMEOUT 1800;

CREATE OR REPLACE TRIGGER std_rsa_timeout
    AFTER SUSPEND ON DATABASE

  BEGIN
    DBMS_RESUMABLE.SET_TIMEOUT(1800);
  END;

  SQL> ALTER SESSION ENABLE RESUMABLE TIMEOUT 1800 NAME 'load_purchase_orders';

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:

  CREATE OR REPLACE TRIGGER rsa_custom
    AFTER SUSPEND ON DATABASE
  DECLARE

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

  BEGIN

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

    NULL;

  END;

  SQL> SELECT user_id, session_id, error_msg FROM dba_resumable;

  USER_ID SESSION_ID ERROR_MSG
  ------- ---------- ---------------------------------------------
       18       1385 ORA-30036: unable to extend segment by 128 in
                     undo tablespace 'USERS'

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

  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 

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.