by Ajay Gursahani

With Oracle 9i's new feature, RESUME, you can suspend or resume operations that run out of space, or reach space limitations after executing for long time. In Oracle 9i we can switch a transaction into 'resumable' mode. This means that any errors arising due to lack of space will not cause a transaction to fail, but to be suspended. When the space problem is fixed, the operation/transaction resumes automatically as if there was no problem earlier.

Say for example, we are trying to copy 200,000 rows and after 100,000 rows our space limitation is reached. Under normal circumstances, an error will be flashed and the rows will be rolled back. When we use the resume option, the transaction will be suspended. After we fix the space problem, the transaction will resume automatically and the transaction will be committed.

For using the RESUME mode we have do the following;

1.      Issue 'GRANT RESUMABLE TO <user>'.

2.      Issue 'ALTER SESSION ENABLE RESUMABLE TIMEOUT <seconds>'

Alternatively for disabling RESUME mode;

1.      Issue 'REVOKE RESUMABLE TO <user>'.

2.      Issue 'ALTER SESSION DISABLE RESUMABLE'

There is also a package, DBMS_RESUMABLE, through which we can set the TIMEOUT in seconds.

Monitoring Suspension Details

When you suspend a transaction, a log is maintained in the alert log. We can use a view, DBA_RESUMABLE, through which we can monitor the progress of the statement and indicate whether the statement is currently executing or suspended.

Structure of DBA_RESUMABLE

SQL> desc dba_resumable;

Name				Null?	     	Type

--------------------------------------       --------     --------------------

USER_ID						NUMBER
SESSION_ID						NUMBER
INSTANCE_ID					        NUMBER
COORD_INSTANCE_ID					NUMBER
COORD_SESSION_ID					NUMBER
STATUS						VARCHAR2(9)
TIMEOUT						NUMBER
START_TIME						VARCHAR2(20)
SUSPEND_TIME					        VARCHAR2(20)
RESUME_TIME					        VARCHAR2(20)
NAME						VARCHAR2(4000)
SQL_TEXT						VARCHAR2(1000)
ERROR_NUMBER					        NUMBER
ERROR_PARAMETER1					VARCHAR2(80)
ERROR_PARAMETER2					VARCHAR2(80)
ERROR_PARAMETER3					VARCHAR2(80)
ERROR_PARAMETER4					VARCHAR2(80)
ERROR_PARAMETER5					VARCHAR2(80)
ERROR_MSG						VARCHAR2(4000)

Example:

In the example below we will do the following;

1.      Create a Tablespace with a small size (1MB) datafile

CREATE TABLESPACE TBS_RESUME
DATAFILE 'E:\ORACLE9I\EXAMPLES\RESUME\TEST_RESUME01.DBF' SIZE 1M;

2.      Create a table which will use the tablespace

CREATE TABLE TAB_RESUME
TABLESPACE TBS_RESUME
AS
SELECT * FROM EMP_EXT WHERE (1=2);

3.      Switch on Resumable mode with Timeout as 1 minute

ALTER SESSION ENABLE RESUMABLE 
TIMEOUT 60 NAME 'PROBLEM 
WITH TABLESPACE: TBS_RESUME';

4.      Insert very large data in the table. The data to be inserted should be greater than 1MB

INSERT INTO TAB_RESUME  (SELECT * FROM EMP_EXT);

Since the data is greater than 1MB the process hangs.

5.      Check the alert log for error. Do not fix the error

Error as in alert.log

statement in resumable session 
'PROBLEM WITH TABLESPACE: 
TBS_RESUME' was suspended due to
ORA-01653: unable to extend table SJM.TAB_RESUME 
by 32 in tablespace TBS_RESUME

Please note that, the alert log displays the text, PROBLEM WITH TABLESPACE: TBS_RESUME, which we specified alongside the NAME clause when firing the ALTER SESSION command

Error as displayed on SQL Prompt after timeout period
SQL> INSERT INTO TAB_RESUME  ( SELECT * FROM EMP_EXT );
INSERT INTO TAB_RESUME  ( SELECT * FROM EMP_EXT )
*
ERROR at line 1:
ORA-30032: the suspended (resumable) statement has timed out
ORA-01653: unable to extend table SJM.TAB_RESUME by 32 in tablespace TBS_RESUME

Since the TIMEOUT specified is 60 seconds we do not get enough time to fix the problem.

6.      Switch on Resumable mode with Timeout as 60 minutes

ALTER SESSION ENABLE RESUMABLE 
TIMEOUT 3600 NAME 
'PROBLEM WITH TABLESPACE: TBS_RESUME'

With 60 minutes, we will have lots of time to fix the problem so that the transaction can be resumed.

7.      Insert very large data in the table. The data to be inserted should be greater than 1MB

INSERT INTO TAB_RESUME  (SELECT * FROM EMP_EXT);

8.      Check the alert log for error. Fix the error by adding one datafile

Error as in alert.log

statement in resumable session 
'PROBLEM WITH TABLESPACE: 
TBS_RESUME' was suspended due to
ORA-01653: unable to extend table SJM.TAB_RESUME 
by 32 in tablespace TBS_RESUME

To fix the problem, add one more datafile to the tablespace. Start another sqlplus session and issue the following command

ALTER TABLESPACE TBS_RESUME 
ADD DATAFILE 'E:\ORACLE9I\EXAMPLES\RESUME\TEST_RESUME02.DBF' 
SIZE 10M;

9.      Check the statement status in the first sqlplus session.

SQL> INSERT INTO TAB_RESUME  (SELECT * FROM EMP_EXT);
56644 rows created.
SQL>

The statement does not hang. It completes the transaction and comes back on the SQL prompt

10.  Check alert.log for any messages.

The alert log shows that the statement resumed after the problem was fixed.


statement in resumable session 'PROBLEM WITH TABLESPACE: TAB_RESUME' was resumed

11.  Check dba_resumable view

SQL> SELECT SQL_TEXT, START_TIME,RESUME_TIME FROM DBA_RESUMABLE;

SQL_TEXT				                START_TIME      RESUME_TIME
------------------------------------------    ----------------------  -----------------
INSERT INTO TAB_RESUME (SELECT * FROM EMP_EXT)    01/30/03 10:24:33  01/30/03 10:28:01
Summary

 

We have discussed how we can use the RESUMABLE option to suspend a transaction and not fail due to space limitations.

Hope my article helps.