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
记录自动清除
