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:
- Space-related error: When there is no more free space in the tablespace or you cannot allocate the next extent
- Maximum extents reached: When the number of extents in the database objects exceeds the maximum extents specified for object.
- 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.
- 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 USERSThe 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:
How to find whether a session is in suspend mode
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.
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.
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.