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:
The following operations are resumable:
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 happens 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
To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
>
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
