How to use Resumable Space Allocation in Oracle 9i

This tip discuss one of new features in Oracle 9i called Resuamable Space Allocation. Before 9i, it was not possible to suspend a session when some space-related problem occured, so as a result whole transaction got rolled back and you had to start your work all over again. This tip discusses how to use this feature in Oracle 9i. Concepts for this tip have been taken from the Oracle manual, which I have have summarized in useful tip format.

Resumable Space Allocation allows you to basically suspend a session if any space allocation error failure occurred. Execution automatically resumes once corrective action is performed. You can take corrective action when the following types of error happen:

  1. Space-related error: When there is no more free space in the tablespace or you cannot allocate the next extent
  2. Maximum extents reached: When the number of extents in the database objects exceeds the maximum extents specified for object.
  3. Quota-related error: When you are allotted a certain quota for the tablespace and you have exceeded that limit.
The following operations are resumable:
  • Queries that run out of temporary space for sorting
  • Insert, Update or Delete statements
  • Export/Import
  • SQL Loader

How to enable a session to be resumable

By default, Oracle sessions are not resumable,

    Requires Free Membership to View

so you have to call the following statement to make your session resumable. However, before calling this you have to have the RESUMABLE privilege:
SQL> REM give RESUMABLE to scott
SQL> connect system/password
SQL> grant RESUMABLE to scott;

SQL> REM  Enable Resumable Feature.
SQL> connect scott/tiger
SQL> alter session enable resumable;
SQL> REM Now your session is not resumable enabled.
Let's look at some real-life examples where the RESUMABLE feature might be quite useful. One example is when you are doing a large processing load -- e.g., a data warehouse load or you are creating a large index and you want to suspend operation if there is out of space error in temp tablespace.

The following example is what happens when you do not use the RESUMABLE feature.

SQL> create table test tablespace users as select * from dba_objects 
     union all select * from dba_objects;

     create table test tablespace users as select * from dba_objects 
     union all select * from dba_objects
ERROR at line 1:
ORA-01652: unable to extend temp segment by 16 in tablespace USERS
The error is reported. It is quite possible that a large load takes a couple of hours and you want to suspend the session if this type of error happens so that you can take corrective action.

Now, let's see what happens when we use the RESUMABLE option.

SQL> alter session enable resumable;

Session altered.

SQL> create table test tablespace users as select * from dba_objects 
     union all select * from dba_objects;
Now, the session will appear like it is hanging which basically means it is in suspend mode. So when I alter my tablespace, this session automatically resumes:
Table created.

How to find whether a session is in suspend mode

Method 1:

Suspend information is logged in the alert.log file. Here it is how it looks in my test case:

ORA-1652: unable to extend temp segment by 16 in tablespace USERS 

Fri Jun 20 23:31:32 2003
statement in resumable session 'User SYSTEM(5), Session 7, Instance 1' was
suspended due to ORA-01652: unable to extend temp segment by 16 in tablespace USERS

Fri Jun 20 23:32:12 2003

Fri Jun 20 23:32:15 2003

Fri Jun 20 23:32:15 2003
statement in resumable session 'User SYSTEM(5), Session 7, Instance 1' was resumed.
Method 2:

You can query the status column in the DBA_RESUMABLE or USER_RESUMABLE views to find out which session are in the SUSPEND state.

Method 3:

You can call the PL/SQL supplied package DBMS_RESUMABLE.SPACE_ERROR_INFO


error_type        OUT VARCHAR2, 
object_type       OUT VARCHAR2, 
object_owner      OUT VARCHAR2, 
table_space_name  OUT VARCHAR2, 
object_name       OUT VARCHAR2, 
sub_object_name   OUT VARCHAR2) 

return boolean;
If it cannot find a space related error, it will return FALSE. Otherwise, TRUE is returned and information about the particular object that caused the space error is returned.

Method 4:

Writing a database-level event trigger: Oracle provides the AFTER SUSPEND event which will fire when any operation is suspeneded.

Create or Replace Trigger Trig_DB_SUSPEND
    /* Your custom code goes here, you could use UTL_SMTP to send yourself
       email about the suspend operation so that you can take corrective action.
       The SQL code written here is autonomous transaction and this is not


How long a session will be suspended

By default, a session is suspended for 7200 seconds (2 Hrs). If you want to suspend it for some other time, do this:


How to assign a custom name to a suspend operation

You may want to use some unique name for a suspened operation; e.g., if you are running five different types of large processing loads and you want to assign different names for the suspended operations so that you can easily see that information in the DBA_RESUMABLE view in the Name column.


How to abort your suspended operation

Sometimes, you need to abort the suspended operation instead of continuing. To do this, you need to use the PL/SQL supplied package DBMS_RESUMABLE.


Export/Import & SQL Loader enhancements

Oracle has enhanced the Export/Import and SQL Loader utilities to use the Resumable feature. These parameters support the resumable feature: RESUMABLE,RESUMABLE_NAME, RESUMEABLE_TIMEOUT. These parameters are self-explanatory. The same parameters also exist in SQL Loader.

How to disable the resumable option for a session

SQL> alter session disable resumable;

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

This was first published in July 2003

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.