Resumable Space Allocation

Don't let space-allocation errors derail your critical jobs—use Oracle9i's resumable space allocation feature to get your jobs back on track.
You're a production analyst two days into the process of running a major job when it comes to an abrupt halt—because of a tablespace blowout. Do you clench every muscle in your body, emit smoke from your ears, and come perilously close to exploding?
Not if you've enabled Oracle9i's resumable space allocation feature. In that case, you breathe a sign of relief, knowing the job is simply suspended, not aborted. Your DBA can calmly add some extra space (or let some preprogrammed code do so) and then watch the job resume automatically.
If you haven't yet used this feature, read on. The how-to information and examples in this article will quickly get you up to speed, so you can turn your space-allocation errors into minor interruptions—instead of major disruptions.

What Is Resumable Space Allocation?


Figure 1: This figure shows a typical sequence of events occurring when an application running in resumable mode is suspended because of a space-allocation error, then resumes because the error is fixed before the timeout period expires.
Resumable space allocation is a feature in Oracle9i that causes space-allocation errors to suspend the running application for a specified timeout period instead of aborting it-providing time for the DBA to correct the problem either manually or through some preprogrammed code in an AFTER SUSPEND trigger. Situations that could cause such errors include blowouts of tables, rollback segments, quotas, and tablespaces. If the space situation is corrected before the timeout ends, execution of the program resumes automatically (see Figure 1); otherwise, the program aborts when the timeout period ends.
This feature is off by default. To use it for a particular session, you must enable resumable mode specifically for that session.
Oracle9i includes the following new additions to support resumable space allocation:
• The RESUMABLE system privilege, which DBAs can grant to application users to allow them to enable resumable mode and manipulate resumable-mode features.
• The RESUMABLE clause of the ALTER SESSION command, which lets you enable and disable resumable mode and change the values of the resumable-mode parameters (TIMEOUT and NAME).
• The command-line parameters RESUMABLE, RESUMABLE_NAME, and RESUMABLE_TIMEOUT, which let you take the same actions as the previously described ALTER SESSION clauses when you are running export, import, and SQL*Loader utilities.
• The DBMS_RESUMABLE package, which lets you retrieve space-error information, manipulate timeout values, and abort suspended statements. See Table 1 for specific subprograms included in this package.
• The DBA_RESUMABLE and USER_RESUMABLE views, which provide information about resumable statements in the system-either all such statements (DBA_RESUMABLE) or those executed by the current user (USER_RESUMABLE). See Table 2 for some specific columns in these views.
Resumable space allocation is fully supported in locally managed tablespaces but has a couple of restrictions in dictionary-managed tablespaces. One is that space errors in rollback segments inside dictionary-managed tablespaces are nonresumable (workarounds here would be to place rollback segments in locally managed tablespaces or to use automatic undo management). The other restriction is that DDL operations such as CREATE TABLE and CREATE INDEX, when used with an explicit MAXEXTENTS clause, may immediately abort the statement if there is not enough room in the tablespace, even in resumable mode (the workaround here is to set MAXEXTENTS to UNLIMITED). While I have mentioned workarounds to these restrictions, keep in mind that Oracle is discouraging the use of dictionary-managed tablespaces for other reasons as well. In general, you'll be better off using locally managed tablespaces.
Basically, I recommend using resumable mode for any session in which there is a possibility of space errors. The three types of errors for which applications can be suspended with this feature—out-of-space errors, max-extents-reached errors, and space-quota-exceeded errors— can occur for many reasons. (See Table 3 for more information on these error types.) They can result from queries (using temporary tablespaces), DML statements (inserts, updates, or deletes), or DDL commands (CREATE TABLE AS SELECT, CREATE INDEX, and so on) that run out of space—as well as from utilities or from PL/SQL or Java applications that use such statements. So, there are many situations in which resumable mode can come in handy.
Now that we have a sense of this feature's usefulness, let's look at some specifics of how to use it.
Enabling and Disabling Resumable Mode
As mentioned earlier, you can turn resumable mode on and off (and specify resumable-mode parameters) in either of two ways: with the RESUMABLE clause of the ALTER SESSION command, or with parameters in the command line.
Prerequisites. Before using either of these methods, you must make sure you have the RESUMABLE system privilege, which DBAs can grant and revoke using the following commands:
GRANT RESUMABLE TO apps;

REVOKE RESUMABLE FROM apps;
Method #1: Using ALTER SESSION. The basic syntax for enabling and disabling resumable mode with the ALTER SESSION command is as follows:
ALTER SESSION ENABLE RESUMABLE;


ALTER SESSION DISABLE RESUMABLE;
You can also add the TIMEOUT and NAME parameters to the RESUMABLE clause to specify values other than the defaults, as follows:
ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600 NAME 'My Code';
Here, we are setting the timeout value at 3,600 seconds (one hour) instead of the default value of two hours, and we are providing a name to identify the suspended code. If we don't specify a NAME clause, the default value for this identifier will be the combination of USER_ID, SESSION_ID, and INSTANCE_ID.
One helpful strategy is to have the DBA create a logon trigger with an ALTER SESSION ENABLE RESUMABLE statement, so that users automatically enter resumable mode when logging in.
Method #2: Using command-line parameters. The resumable-mode command-line parameters are particularly useful when you are running import and export utilities, since space-allocation errors can easily occur during imports and loads (which can blow out tables, tablespaces, and so on), as well as during exports (which can blow out rollback segments). You can use these parameters as follows:
• Use RESUMABLE=Y to enable resumable mode.
• Use RESUMABLE_NAME=name to set a name value.
• Use RESUMABLE_TIMEOUT=n (in seconds) to set a timeout value.
Modifying the Resumable-Mode Parameters
You can modify the resumable-mode parameters in either of two ways. The first way is simply to issue an ALTER SESSION command specifying a new value for the parameter you want to change, as in these examples:
ALTER SESSION ENABLE RESUMABLE NAME 'inserting into EMP';

ALTER SESSION ENABLE RESUMABLE TIMEOUT 1800;
The second way to make a change (to the timeout parameter only, not to the name parameter) is to use the SET_TIMEOUT or SET_SESSION_TIMEOUT function from the DBMS_RESUMABLE package. In the following example, we are setting a timeout of two minutes in the current session:
DBMS_RESUMABLE.SET_TIMEOUT(120);
In the next example, we are setting a timeout of 10,000 seconds in session 233:

DBMS_RESUMABLE.SET_SESSION_TIMEOUT (233, 10000);
The DBMS_RESUMABLE package also includes a GET_TIMEOUT function and a GET_SESSION_TIMEOUT function; these return the current timeout values for the current session and a specified session, respectively. (See Table 1 for information about other functions and procedures in this package.) You can use these functions for checking the timeout value during a suspension and extending the timeout as needed.
Dealing with Suspended Statements
Once you've enabled resumable mode and set appropriate values for the resumable-mode parameters, it's time to think about how you want to handle suspended statements when they occur. You can either fix space allocations manually or preprogram AFTER SUSPEND triggers to handle the situation. Most DBAs prefer manual intervention, especially in mission-critical databases, because various factors may be interacting and may require actions such as contacting end users and managing physical and logical space. However, AFTER SUSPEND triggers can be useful for handling space errors in noncritical situations—and you can also use them just to send an e-mail notifying yourself or someone else that an error has occurred. (The standard notification is for the user to receive an error message, which is also indicated in the alert log.)
As a side note, it is possible under certain unusual circumstances for a suspended application to resume successfully with no intervention at all. Suppose application A is executing a long-running query in resumable mode (with a fairly long timeout value) against a temporary tablespace. Application B then begins to execute a query against the same tablespace. Out of the blue, B allocates all available extents that were also available to A. Application A immediately enters suspended mode and remains in that mode until B finishes its query, clearing up its used space and leaving room for A to continue. It is heartening to know such things can happen, but don't count on them happening very often—in most cases, the space condition won't get fixed without intervention of some type.
Fixing space allocations manually. The first thing you need to do when you are notified of a suspended statement is to find out as much as you can about what caused the space error. In addition to checking the USER_RESUMABLE and DBA_RESUMABLE views (see Table 1 for more information about these), you can look up V$SESSION_WAIT, which shows wait-time information on events, to track the condition of the suspended statement. V$SESSION_WAIT displays the "suspended on space" string in the EVENT column.
Correcting the space situation may involve various different actions, depending on what caused the error. For example, you might need to increase the size of a tablespace, set a higher MAXEXTENTS value for a segment, or enlarge a user's quota. Keep in mind that resources relative to the suspended statement are locked until the statement resumes, so contention issues may arise. If the first fix you attempt doesn't work, don't worry—the same statement can be suspended and resumed multiple times.
Sometimes, you may find that you need to stop the application instead of treating the error. If so, simply abort the execution of a suspended statement using the abort procedure from the DBMS_RESUMABLE package, as follows:
DBMS_RESUMABLE.ABORT(session id);
In the more likely (and preferred) case that you fix the space problem successfully, the application will automatically resume execution-assuming you do this before the user gets the dreaded message "ORA-30032: the statement has timed out."
Using AFTER SUSPEND triggers. If there may not be anyone available to fix errors manually, or if you want to add extra notification steps when suspensions occur (such as sending an e-mail to the person who will fix the error), simply use an AFTER SUSPEND trigger. (Make sure to create this trigger under the SYS schema.) The basic form of such a trigger is as follows:
CREATE OR REPLACE TRIGGER fix_space
AFTER SUSPEND ON DATABASE
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

/* Treat the error or send notification here*/

END;
Of course, you will not know in advance what the exact conditions will be of the space errors that will occur. You can have the trigger get information about the error using the DBMS_RESUMABLE package or the USER_RESUMABLE or DBA_RESUMABLE views. Then, the trigger can take an appropriate action based on this information—keeping in mind that SQL statements made inside the trigger are nonresumable.
A Simple Example: Importing an Overly Large Table
User Scott is importing an 80MB EMP table into datafile data01.dbf. This file resides in an empty, locally managed tablespace called DATA01. Unfortunately, DATA01 has only 50MB of free space. It's a good thing Scott always imports with the resumable-space-allocation feature enabled, as follows:
$ imp scott/tiger file=expdata.dmp tables=emp
resumable=y resumable_name=test resumable_timeout=120
Scott is now headed for an inevitable space error, but the error will cause the import to suspend temporarily, rather than aborting immediately—giving you, as DBA, a two-minute chance to fix the problem. (Scott's a fast-action type who doesn't like resources to stay locked during the default two-hour timeout period.) Let's look at three different scenarios for what happens next, based on whether you intervene to fix the error and on how you intervene. These scenarios include: no intervention, manual intervention, and intervention by preprogrammed trigger.
Scenario #1: No intervention. Well, okay, maybe two minutes is a bit too brief. Let's say you aren't able to get in and fix the error that quickly. Should this happen, you'd see the following set of messages leading to termination of the import operation:
IMP-00058: ORACLE error 30032 encountered
ORA-30032: the suspended (resumable) statement has timed out
ORA-01653: unable to extend table SCOTT.EMP by 8 in tablespace DATA01
IMP-00028: partial import of previous table rolled back:
163 rows rolled back
Import terminated successfully with warnings.
Scenario #2: Manual intervention. Ideally, when the import operation is suspended, you are notified quickly (perhaps by a triggered e-mail) and are able to attend to the problem instantly. You open a concurrent session and check the error information as follows:
SQL> SELECT name, resume_time, error_msg from dba_resumable;

NAME RESUME_TIME ERROR_MSG
------ ----------- ------------------------------------

TEST ORA-01653: unable to extend table
SCOTT.EMP by 8 in tablespace DATA01
(Note that this information is available only until the timeout interval expires; after that, if you issued this SELECT command, you would receive a "no rows selected" message.)
Once you've seen this, your lightning-like reflexes take over; you add more space just in the nick of time! However, if your reflexes aren't quite that lightning-like, you'd probably be more successful in short-timeout situations by setting up a trigger in advance to handle such problems.
Scenario #3: Intervention by preprogrammed trigger. Let's say you knew beforehand about Scott's penchant for short timeouts. You might then have created an AFTER SUSPEND trigger to change the original timeout of suspended applications to a more reasonable length of one hour, as shown here:
CREATE OR REPLACE TRIGGER change_timeout
AFTER SUSPEND
ON DATABASE
BEGIN
DBMS_RESUMABLE.SET_TIMEOUT(3600);
END;
However, let's say you decided to go even further and make a trigger that would handle the space situation itself. Specifically, you created the following AFTER SUSPEND trigger to react to space shortages automatically by resizing the data01.dbf datafile from 50MB to 100MB:
CREATE OR REPLACE TRIGGER fix_space
AFTER SUSPEND ON DATABASE
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE
'ALTER DATABASE
DATAFILE ''$ORACLE_HOME/oradata/my_app/data01.dbf''
RESIZE 100 M';
END;
Now, when Scott tries to import the 80MB file into DATA01, with its mere 50MB of free space, the data01.dbf file and the DATA01 tablespace are expanded to 100MB. The import then resumes automatically, with the following message appearing after it ends.
Import terminated successfully without warnings.


**Winning the Add-More-Space Race
Given how easy it is to use Oracle9i's new resumable-space-allocation feature, there's no reason to let yourself get skewered by space errors anymore. Just set up any AFTER SUSPEND triggers you might want, turn on resumable mode, and relax. If a space error does occur, you know that program execution will suspend and give you the chance to come in and fix the error—before it fixes you.
Andre Whittick Nasser (andre.nasser@primeturn.com) is the Chief Technology Officer at PrimeTurn.com, a consulting firm specializing in high-end corporate technology in Brazil. Andre is an OCP, and he was featured in the "Peer to Peer" section of the May/June 2001 issue of Oracle Magazine.
TABLE 1:
Procedure or Function Description
ABORT (session id) This procedure aborts the suspended statement.
GET_SESSION_TIMEOUT (session id) This function returns the timeout value for the specified session.
SET_SESSION_TIMEOUT (session id, timeout) This procedure sets a timeout value for a session. Its effect is immediate.
GET_TIMEOUT This function returns the timeout value for the current session.
SET_TIMEOUT (timeout) This procedure sets the timeout value for the current session.
SPACE_ERROR_INFO (error_type, object_type, object_owner, table_space_name, object_name, sub_object_name) All parameters in this procedure are OUT parameters and provide information on the suspended statement.

Procedures and functions included in the DBMS_RESUMABLE package. (Note that timeout values are expressed in seconds.)
TABLE 2:
Column Description
USER_ID User ID number of user executing the statement. (Shown only in DBA_RESUMABLE).
SESSION_ID Session ID of resumable statement.
INSTANCE_ID Instance number of resumable statement.
SQL_TEXT First 1,000 characters of resumable statement.
NAME Name given to resumable statement.
STATUS Status of resumable statement: RUNNING, SUSPENDED, ABORTED, ABORTING, or TIMOUT.
ERROR_NUMBER Error number caused by suspended statement.
ERROR_MSG Literal error message corresponding to ERROR_NUMBER.
START_TIME Start time (local) of resumable statement.
SUSPEND_TIME Last time the resumable statement was suspended.
RESUME_TIME Last time the statement was resumed.
TIMEOUT Timeout value for the statement (in seconds).

Selected columns from the DBA_RESUMABLE and USER_RESUMABLE views. (For a complete description of these views, see the Oracle9i Database Reference manual.)

TABLE 3:
Error Type Error Condition Sample Error Message
Out-of-space error The operation cannot acquire any more extents in the tablespace for the table, index, temporary segment, rollback segment, undo segment, cluster, LOB, table partition, or index partition. ORA-1650 unable to extend rollback segment ... in tablespace ...
Max-extents-reached error The number of extents in a table (or index, temporary segment, rollback segment, undo segment, cluster, LOB, table partition, or index partition) equals the maximum extents defined for the object. ORA-1631 max # extents ... reached in table ...
Space-quota-exceeded error The user has exceeded his or her assigned space quota in the tablespace. ORA-1536 space quota exceeded for tablespace string

Types of errors treatable with the resumable-space-allocation feature.