How to use Resumable Space Allocation in Oracle 9i

Here's 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
  • DDL statements like CREATE TABLE AS SELECT, ALTER TABLE, CREATE INDEX, etc.
  • Export/Import
  • SQL Loader

How to enable a session to be resumable

By default, Oracle sessions are not resumable, 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
/* OracleOEM */ ALTER DATABASE DATAFILE 'C:\ORACLE9I\ORADATA\SANLAPT\USERS01.DBF' RESIZE  20M

Fri Jun 20 23:32:15 2003
Completed: /* OracleOEM */ ALTER DATABASE DATAFILE 'C:\ORACLE

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

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
AFTER SUSPEND ON DATABASE
DECLARE
BEGIN
    /* 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
       resumable.*/

END;

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:

ALTER SESION ENABLE RESUMABLE TIMEOUT 3600;

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.

ALTER SESION ENABLE RESUMABLE TIMEOUT 3600 NAME 'LOAD_1';

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.

SQL> exec DBMS_RESUMABLE.ABORT (session_id IN NUMBER);

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

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close