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

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

ALTER SESSION ENABLE RESUMABLE

TIMEOUT 3600 NAME

‘PROBLEM WITH TABLESPACE: TBS_RESUME’

INSERT INTO TAB_RESUME  (SELECT * FROM EMP_EXT);

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

ALTER TABLESPACE TBS_RESUME

ADD DATAFILE ‘E:ORACLE9IEXAMPLESRESUMETEST_RESUME02.DBF’

SIZE 10M;

SQL> INSERT INTO TAB_RESUME  (SELECT * FROM EMP_EXT);

56644 rows created.

SQL>

statement in resumable session ‘PROBLEM WITH TABLESPACE: TAB_RESUME’ was resumed

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