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

Advertisements

How to set/find default tablespace in 10g?

— Set default tablespace for all users, so when users are created without specifying default tablespace it will be user’s default tablespace
SQL> ALTER DATABASE DEFAULT TABLESPACE abc;

Database altered.

— To find the default tablespace name for the database is
SQL> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = ‘DEFAULT_PERMANENT_TABLESPACE’;

PROPERTY_VALUE
——————
ABC

Once when default tablespace is set for the database it can’t be dropped until it’s changed to a different tablespace otherwise one will get ORA-12919 when trying to drop the tablespace
SQL> drop tablespace abc including contents and datafiles;
drop tablespace abc including contents and datafiles
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace

So changing the default tablespace for the database will change default tablespace for users who have that tablespace as default tablespace. The following demonstrates what happens when default tablespace is changed for the database.

— set default tablespace to be users
SQL> ALTER DATABASE DEFAULT TABLESPACE users;

Database altered.

— create user with tablespace users as default
SQL> create user test1 identified by test1 default tablespace users;

User created.

— change default tablespace for the database
SQL> ALTER DATABASE DEFAULT TABLESPACE abc;

Database altered.

— it changes default tablespace for user TEST1
SQL> select default_tablespace from dba_users where username = ‘TEST1’;

DEFAULT_TABLESPACE
——————————
ABC

By default if default tablespace is not specified the default tablespace is set to SYSTEM, so you can specify the default tablespace when creating the database (For example: CREATE DATABASE … DEFAULT TABLESPACE abc…)