resumable oracle,oracle学习笔记 RESUMABLE

RESUMABLE在以下条件中挂起当前操作:

Out of space

Maximum extents reached

Space quota exceeded

当错误条件处理以后，事务会继续执行。

常见错误：

Out of space condition

The operation cannot acquire any more extents for a table/index/temporary segment/undo segment/cluster/LOB/table partition/index partition in a tablespace. For example, the following errors fall in this category:

ORA-1653 unable to extend table ... in tablespace ...

ORA-1654 unable to extend index ... in tablespace ...

Maximum extents reached condition

The number of extents in a table/index/temporary segment/undo segment/cluster/LOB/table partition/index partition equals the maximum extents defined on the object. For example, the following errors fall in this category:

ORA-1631 max # extents ... reached in table ...

ORA-1654 max # extents ... reached in index ...

Space quota exceeded condition

The user has exceeded his assigned space quota in the tablespace. Specifically, this is noted by the following error:

ORA-1536 space quote exceeded for tablespace string

演示:

SQL> select * from system_privilege_map where name='RESUMABLE';

PRIVILEGE NAME PROPERTY

---------- --------------- ----------

-236 RESUMABLE 0

SQL> select * from session_privs where privilege='RESUMABLE';

PRIVILEGE

----------------------------------------

RESUMABLE

SQL>

SQL> select tablespace_name,autoextensible from dba_data_files where tablespace_name='TBS_HUNT';

TABLESPACE_NAME AUT

--------------- ---

TBS_HUNT NO

SQL> select tablespace_name,bytes/1024/1024 from dba_data_files where tablespace_name='TBS_HUNT';

TABLESPACE_NAME BYTES/1024/1024

--------------- ---------------

TBS_HUNT 10

SQL> alter session enable resumable;

Session altered.

SQL> alter session set resumable_timeout=600;

Session altered.

SQL> insert into t select object_id,object_name from dba_objects;

50400 rows created.

SQL> insert into t select object_id,object_name from dba_objects;

50400 rows created.

SQL> insert into t select object_id,object_name from dba_objects;

----此时空间不足，未报错。

Alter.log 报：

statement in resumable session 'User ADMIN(61), Session 152, Instance 1' was suspended due to

ORA-01653: unable to extend table ADMIN.T by 128 in tablespace TBS_HUNT

超过600S 以后报：

RROR at line 1:

ORA-30032: the suspended (resumable) statement has timed out

ORA-01653: unable to extend table ADMIN.T by 128 in tablespace TBS_HUNT

SQL> select object_name,reason from dba_outstanding_alerts;

OBJECT_NAME

-------------------------

REASON

--------------------------------------------------------------------------------

TBS_HUNT

Operation on resumable session User ADMIN(61), Session 152, Instance 1 session i

d 152 suspended because of errors in tablespace TBS_HUNT. Error message is ORA-0

1653: unable to extend table ADMIN.T by 128 in tablespace TBS_HUNT

Configuration

Metrics "Database Time Spent Waiting (%)" is at 97.36488 for event class "Config

uration"

SQL> insert into t select object_id,object_name from dba_objects;

---登录另一窗口执行：

SQL> alter database datafile 'E:\oracle\product\10.2.0\oradata\ORCL\TBS_HUNT001.DBF' autoextend on;

过一会可以看到insert 成功。

SQL> select * from dba_outstanding_alerts;

no rows selected

记录自动清除