Resumable timeout explained

One can make Oracle suspend a session if it runs out a free space by enabling resumable timeout. This can be set by as follows and the value is in seconds:
— Set a session timeout to be 1800
SQL> alter session enable resumable timeout 1800;
Session altered.

— Example: Set system wide resuamble timeout to be 1800 sec
SQL> ALTER SYSTEM SET RESUMABLE_TIMEOUT=1800;
System altered.

Demo: Shows a session suspended when resumable timeout is set. In the following example the session was suspended as it ran out of space on a tablespace
SQL> insert into a (select * from a);

Message in the alert_TESTDB.log when a session runs out of space
Mon Aug 01 13:49:55 2011
statement in resumable session ‘User SYS(0), Session 194, Instance 1’ was suspended due to
ORA-01653: unable to extend table SYS.A by 128 in tablespace SYSTEM
insert into a (select * from a)
*

There is a view that contains information on sessions that are and were suspended, in the example below it shows the STATUS is SUSPENDED and time when it occurred in the above example
SQL> select user_id, instance_id, status, name, error_msg, suspend_time, resume_time from dba_resumable;

USER_ID INSTANCE_ID STATUS
———- ———– ———
NAME
——————————————————————————–
ERROR_MSG
——————————————————————————–
SUSPEND_TIME RESUME_TIME
——————– ——————–
0 1 SUSPENDED
User SYS(0), Session 194, Instance 1
ORA-01653: unable to extend table SYS.A by 128 in tablespace SYSTEM
08/01/11 13:49:54
ERROR at line 1:
ORA-30032: the suspended (resumable) statement has timed out
ORA-01653: unable to extend table SYS.A by 128 in tablespace SYSTEM

Information in V$SESSION about session waiting for the tablespace to be extended, the status is ACTIVE and event is “statement suspended…”
SQL> select status, state, event FROM v$session where sid = 194;

STATUS
——————-
STATE
——————-
EVENT
—————————————————————-
ACTIVE
WAITING
statement suspended, wait error to be cleared

Also this event is recorded in v$session_event
SQL> select * FROM v$session_event where sid = 194 and EVENT like ‘statement%’;

SID EVENT
———- —————————————————————-
TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO
———– ————– ———– ———— ———- —————–
EVENT_ID WAIT_CLASS_ID WAIT_CLASS#
———- ————- ———–
WAIT_CLASS
—————————————————————-
194 statement suspended, wait error to be cleared
254 254 50788 199.95 200 507883018
680822103 3290255840 2
Configuration

If the tablepsace is not fixed within the timeout, the session will error out and the status is TIMEOUT for the session in DBA_RESUMABLE
SQL> select user_id, session_id, instance_id, status, name, error_msg, start_time, suspend_time, resume_time from dba_resumable;

USER_ID SESSION_ID INSTANCE_ID STATUS
———- ———- ———– ———
NAME
——————————————————————————–
ERROR_MSG
——————————————————————————–
START_TIME SUSPEND_TIME RESUME_TIME
——————– ——————– ——————–
0 194 1 TIMEOUT
User SYS(0), Session 194, Instance 1
ORA-01653: unable to extend table SYS.A by 128 in tablespace SYSTEM
08/01/11 13:49:54 08/01/11 13:49:54

And also message gets recorded in the alert.log when the sessinon times out
Mon Aug 01 14:19:55 2011
statement in resumable session ‘User SYS(0), Session 194, Instance 1’ was timed out

If the tablepsace is fixed within the timeout, the session will continue to run the status is NORMAL for the session in DBA_RESUMABLE with resume_time set to indicate when the session start running again
USER_ID SESSION_ID INSTANCE_ID STATUS
———- ———- ———– ———
NAME
——————————————————————————–
ERROR_MSG
——————————————————————————–
START_TIME SUSPEND_TIME RESUME_TIME
——————– ——————– ——————–
0 194 1 NORMAL
User SYS(0), Session 194, Instance 1

08/01/11 15:05:53 08/01/11 15:06:19

And in the alert.log
Mon Aug 01 15:06:16 2011
alter database datafile ‘E:\ORACLE\TESTDB\ORADATA\SYSTEM.DBF’ AUTOEXTEND ON
Completed: alter database datafile ‘E:\ORACLE\TESTDB\ORADATA\SYSTEM.DBF’ AUTOEXTEND ON
Mon Aug 01 15:06:19 2011
statement in resumable session ‘User SYS(0), Session 194, Instance 1’ was resumed

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s