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.

• 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: 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 happe...

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Oracle database administrator Understanding SQL string functions What is the difference between a database engineer, architect and administrator? Import on one table from dump file Error during RMAN backup Can I drop a column in SYS schema? STATSPACK tool: transaction vs. execution measurement Should I port from Microsoft Access? How can I find statistics on total memory usage and database connections? Installing multiple Oracle homes Modifying SYS password in a RAC environment

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

ns when you do not use the RESUMABLE feature. 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. 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:

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: 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 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.

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

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.