New Features / Documentos / Soporte Técnico / Servicios / Tilsor
 
 

Using RESUMABLE Session to Avoid Transaction Abort Due to Space Errors

Creation Date: 14-MAR-2001
Last Revision Date: 29-APR-2002

PURPOSE
-------
This document explains the cases when the resumable space allocation feature
can be useful.
In case a transaction is suspended for space allocation reasons,the resumable
space allocation feature allows the transaction to be resumed.


SCOPE & APPLICATION
-------------------

These are very common situations when a transaction is interrupted for a space
allocation issue:

* storage settings inappropriate for the table being inserted
* storage settings inappropriate for the rollback segment being used for the
transaction
* lack of space on temporary tablespace
* user quotas restricted
* users privileges missing

The resumable statement feature allows the DBA, once having applied the
appropriate solution to the space allocation issue, to resume the suspended
transaction which does not loose all the work done previously.


How to be able to resume a transaction facing a space allocation issue
---------------------------------------------------

1/ The user running the transaction requires the RESUMABLE system privilege :

SQL> connect system/manager
Connected.

SQL> grant resumable to scott;
Grant succeeded.

2/ Set the session so that the following transactions might be resumed
in case of interruption due to space allocation:

SQL> alter session enable resumable;
Session altered.

This can be set automatically through an AFTER LOGON trigger.

SQL> create or replace trigger logon_set_resumable
2 after logon
3 on scott.schema
4 begin
5 execute immediate 'alter session enable resumable timeout 1200';
6 end;
7 /

Trigger created.

3/ While inserting new rows into TEST_RESUMABLE table, the user session hangs,
but the transaction does not roll back:

The DBA can retrieve the reason why the session of user SCOTT hangs in
DBA_RESUMABLE view:

a. Displaying the DBA_RESUMABLE view:

SQL> select user_id,SESSION_ID, STATUS, START_TIME, SUSPEND_TIME,
2 SQL_TEXT, ERROR_NUMBER, ERROR_MSG
3 from dba_resumable;

USER_ID SESSION_ID STATUS START_TIME SUSPEND_TIME
---------- ---------- --------- ----------------------
SQL_TEXT
----------------------------------------------------
ERROR_NUMBER
------------
ERROR_MSG
----------------------------------------------------
54 9 SUSPENDED 03/14/01 10:49:25 03/14/01 11:14:17
insert into test_resumable select * from test_resumable
1631
ORA-01631: max # extents (5) reached in table SCOTT.TEST_RESUMABLE


b. In alert.log file:

Wed Mar 14 11:14:17 2001
statement in resumable session 'User SCOTT(54), Session 9, Instance 1' was
suspended due to
ORA-01631: max # extents (5) reached in table SCOTT.TEST_RESUMABLE


c. The statement may issue the following error when the timeout set for the
session has expired:

SQL> insert into test_resumable values (1);
insert into test_resumable values (1)
*
ERROR at line 1:
ORA-30032: the suspended (resumable) statement has timed out
ORA-01536: space quota exceeded for tablespace 'EXAMPLE'

4/ The DBA now knows why the session hangs, and needs to find which action to
take to alleviate the ora-1631 error:

SQL> connect system/manager
Connected.

SQL> alter table scott.test_resumable storage (maxextents 8);
Table altered.

In alert.log file:

Wed Mar 14 11:24:02 2001
statement in resumable session 'User SCOTT(54), Session 9, Instance 1' was
resumed

and no more errors in DBA_RESUMABLE view:

SQL> select user_id,SESSION_ID, STATUS, START_TIME, RESUME_TIME,
2 SQL_TEXT, ERROR_NUMBER, ERROR_MSG
3 from dba_resumable;

USER_ID SESSION_ID STATUS START_TIME RESUME_TIME
---------- ---------- --------- -------------------- ---
SQL_TEXT
-----------------------------------------------------------
ERROR_NUMBER
------------
ERROR_MSG
----------------------------------------------------------
54 9 NORMAL 03/14/01 10:49:25 03/14/01 11:24:02
insert into test_resumable select * from test_resumable
0

While the status is NORMAL or the error_number is 0, the resumable statements
keep on working correctly unless the timeout is expired.
This also means that there are sessions set in resumable state.
As soon as an error_number <> 0 appears, then a resumable session has
encountered a space allocation issue.

Note:
The DBA can cancel the resumable transaction by aborting the session by the
procedure DBMS_RESUMABLE.ABORT(sid#). An ORA-1013 "user requested cancel of
current operation" is returned to the user.

5/ If the session does not need to be in resumable state, the session can
disable the resumable state:

SQL> alter session disable resumable;
Session altered.

SQL> select user_id,SESSION_ID, STATUS, START_TIME, RESUME_TIME,
2 SQL_TEXT, ERROR_NUMBER, ERROR_MSG
3 from dba_resumable;

no rows selected


Other space errors that suspend transactions
--------------------------------------------

***
statement in resumable session 'User SCOTT(54), Session 9, Instance 1' was
suspended due to
ORA-01536: space quota exceeded for tablespace 'EXAMPLE'

***
statement in resumable session 'User SCOTT(54), Session 8, Instance 1' was
suspended due to
ORA-01562: failed to extend rollback segment number 11

***
statement in resumable session 'User SCOTT(54), Session 8, Instance 1' was
suspended due to
ORA-01628: max # extents (2) reached for rollback segment RS01
FULL status of rollback segment 11 set

***
statement in resumable session 'User SCOTT(54), Session 9, Instance 1' was
suspended due to
ORA-01631: max # extents (2) reached in table SCOTT.TEST_RESUMABLE

***
statement in resumable session 'User SYSTEM(5), Session 8, Instance 1' was
suspended due to
ORA-01652: unable to extend temp segment by 32 in tablespace TEMP_TS

***
statement in resumable session 'User SCOTT(54), Session 9, Instance 1' was
suspended due to
ORA-01653: unable to extend table SCOTT.TEST_RESUMABLE by 256 in tablespace USERS

***
statement in resumable session 'User SCOTT(34), Session 8, Instance 1' was
suspended due to
ORA-01654: unable to extend index SCOTT.SYS_IOT_TOP_27956 by 8 in tablespace PERM_DICT_2K

***
statement in resumable session 'User SYSTEM(5), Session 11, Instance 1' was
suspended due to
ORA-01658: unable to create INITIAL extent for segment in tablespace LMT_1

***
statement in resumable session 'User SYSTEM(5), Session 11, Instance 1' was
suspended due to
ORA-01659: unable to allocate MINEXTENTS beyond 42 in tablespace LMT_1


Other messages in alert.log:
***
Wed Mar 14 10:43:52 2001
statement in resumable session 'User SCOTT(54), Session 9, Instance 1' was
aborted


Related Documents
-----------------
[NOTE:161015.1] Known Problems with Resumable Space Allocation
[NOTE:152013.1] What is the use of the RESUMABLE parameter in EXPORT ?
[NOTE:164850.1] ORA-1652 in Resumable Statements Prevents any SELECT on
DBA_RESUMABLE View