RESUMABLE SESSION

RESUMABLE SESSION

Let you enable and disable resumable space allocation. This feature allows an operation to be suspended in the event of an out-of-space error condition and to resume automatically from the point of interruption when the error condition is fixed.Using RESUMABLE Session to Avoid Transaction Abort Due to Space Errors

Note:

Resumable space allocation is fully supported for operations on locally managed tablespaces. Some restrictions apply if you are using dictionary-managed tablespaces.

 

一.one session

1. grant resumable to user

2. alter session enable resumable timeout  1200

3.SQL> create table t_resume tablespace myts as select * from all_objects; ------halt and there is no error appear.

       ORA-01652: unable to extend temp segment by 8 in tablespace MYTS

二 another session;

1.SQL> alter database datafile '/u01/oradata/lsh/myts01.dbf' resize 50m;

三 one session

 SQL> create table t_resume tablespace myts as select * from all_objects;

表已创建。

ENABLE RESUMABLE：

This clause enables resumable space allocation for the session.

TIMEOUT TIMEOUT： lets you specify (in seconds) the time during which an operation can remain suspended while waiting for the error condition to be fixed. If the error condition is not fixed within the TIMEOUT period, then Oracle Database aborts the suspended operation.

NAME： NAME lets you specify a user-defined text string to help users identify the statements issued during the session while the session is in resumable mode. Oracle Database inserts the text string into the USER_RESUMABLE and DBA_RESUMABLE data dictionary views. If you do not specify NAME, then Oracle Database inserts the default string 'User username(userid), Session sessionid, Instance instanceid'.