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.
Back to DBAsupport.com
