Using Resume Operation in Oracle 9i


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

Latest Articles