想象一下下面的场景：

一个跑了四、五个小时的批量 INSERT ，眼看到了最后一步，屏幕上突然蹦出：

ORA-01653: unable to extend table TEST.T_RESUMABLE by 128 in tablespace SMALL_TBS

空间不够，语句报错，前面几个小时的劳动全部回滚，只能加好空间从头再来。如果把批量作业安排在夜里，第二天早上迎接你的就是这次彻底的失败。

其实 Oracle 早在 9i 时代就为这类场景准备了一个非常冷门但极其贴心的特性 —— 可恢复语句（ Resumable Statement ），以及配套的 DBMS_RESUMABLE 包：遇到空间类错误时，语句不是立即报错，而是挂起等待； DBA 趁机把空间补上，语句自动从断点继续执行，就像什么都没发生过。

工作原理是开启可恢复模式后，会话中的语句在遇到空间类错误时不会直接失败，而是进入挂起状态：

语句所在的事务不回滚，行锁、 undo 等资源继续持有；

数据库在告警日志中记录挂起事件，DBA_RESUMABLE 视图中可以看到挂起的会话、原因和错误信息；

挂起的会话在 V$SESSION_WAIT 中表现为等待事件 statement suspended, wait error to be cleared ，所以从监控上看它就像 " 卡住 " 了一样；

一旦空间问题被解决（扩数据文件、加数据文件、扩配额），语句自动恢复执行，无需任何人工干预；

如果一直到超时时间（默认 7200 秒，即 2 小时）问题都没有解决，语句才会真正报出原始错误并回滚。

可以触发挂起的三类错误条件：

空间耗尽（表、索引、临时段、undo 等） ORA-01653 （表无法扩展）、 ORA-01654 （索引无法扩展）

达到最大扩展数 ORA-01631 、 ORA-01632

配额超限 ORA-01536: space quota exceeded for tablespace

注意：只有空间类错误会挂起。锁等待、ORA-01555 之类的错误照常抛出，这个特性管不了。 SELECT 语句（比如大排序把临时表空间耗尽）同样受益于该特性。

如何开启

前提：用户必须拥有 RESUMABLE 系统权限（包含在 DBA 角色中），因为挂起的语句会一直持有系统资源。

方式一：会话级开启（最常用）

 SQL> alter session enable resumable timeout 600 name 'fill_small_tbs';
 Session altered.
TIMEOUT 不写时默认 7200 秒；
NAME 用于在 DBA_RESUMABLE 中标识这个挂起的作业，方便定位。

方式二：实例级开启

10g 起提供了 RESUMABLE_TIMEOUT 初始化参数（默认 0 ，即关闭），可以直接设置，也可以动态修改：

SQL> alter system set resumable_timeout = 3600;
 
System altered.

方式三：给特定用户默认开启

通过 LOGON 触发器对该用户的会话自动执行 ALTER SESSION ENABLE RESUMABLE ，适合 " 某个账号专门跑批 " 的场景。

两点提醒：开启动作只对之后开始的语句生效，救不了正在报错的语句；挂起期间锁和 undo 都不释放，所以不要在 OLTP 高并发会话上无差别开启 —— 它是为批量作业、索引重建、大数据导入这类场景准备的。

DBMS_RESUMABLE 包结构

SQL> desc dbms_resumable
PROCEDURE ABORT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SESSION_ID                     NUMBER                  IN
FUNCTION GET_SESSION_TIMEOUT RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SESSION_ID                     NUMBER                  IN
PROCEDURE SET_SESSION_TIMEOUT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SESSION_ID                     NUMBER                  IN
 TIMEOUT                        NUMBER                  IN
PROCEDURE SET_TIMEOUT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 TIMEOUT                        NUMBER                  IN
FUNCTION SPACE_ERROR_INFO RETURNS BOOLEAN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ERROR_TYPE                     VARCHAR2                OUT
 OBJECT_TYPE                    VARCHAR2                OUT
 OBJECT_OWNER                   VARCHAR2                OUT
 TABLE_SPACE_NAME               VARCHAR2                OUT
 OBJECT_NAME                    VARCHAR2                OUT
 SUB_OBJECT_NAME                VARCHAR2                OUT

各子程序的用途：

ABORT(sid) 人为中止一个挂起的语句（调用者需是会话属主，或拥有 ALTER SYSTEM/DBA 权限）

SET_TIMEOUT(n) 修改当前会话的超时时间，立即生效

GET_SESSION_TIMEOUT / SET_SESSION_TIMEOUT 查看 / 修改指定会话的超时时间

SPACE_ERROR_INFO 只能在 AFTER SUSPEND 触发器里调用，返回挂起原因的详细信息

SPACE_ERROR_INFO 有两个容易踩坑的点：一是它的参数里没有错误号，错误号要从 DBA_RESUMABLE.ERROR_NUMBER 拿；二是 ERROR_TYPE 的取值是三类字符串之一 ——NO MORE SPACE 、 MAX EXTENTS REACHED 、 SPACE QUOTA EXCEEDED 。

实测：完整复现一次挂起与恢复

1. 准备一个 5M 的小表空间（DBA）
SQL> create tablespace small_tbs datafile '/oracle/app/oradata/orcl/small01.dbf' size 5m;
 
Tablespace created.
2. 给测试用户授权（DBA）
 
sql
SQL> grant resumable to test;
 
Grant succeeded.
3. 建表并开启可恢复模式（测试用户）
 
sql
SQL> create table t_resumable(id number, pad varchar2(4000)) tablespace small_tbs;
 
Table created.
 
SQL> alter session enable resumable timeout 600 name 'fill_small_tbs';
 
Session altered.
4. 制造空间耗尽
 
sql
SQL> begin
  2    for i in 1 .. 10000 loop
  3      insert into t_resumable values (i, rpad('x', 4000, 'x'));
  4    end loop;
  5    commit;
  6  end;
  7  /
插入几千行之后 5M 空间耗尽。如果没开可恢复模式，这里会立刻报 ORA-01653 并把整个 PL/SQL 块的插入全部回滚；现在这个会话只是安静地停在那里，一直运行到把空间补上为止。
 
5. 另一个会话观测挂起状态（DBA）
 
sql
SQL> select session_id, name, status, timeout, error_number, error_msg
  2  from dba_resumable;
 
SESSION_ID NAME              STATUS      TIMEOUT ERROR_NUMBER
---------- ----------------- ----------- ------- ------------
ERROR_MSG
----------------------------------------------------------------
       137 fill_small_tbs    SUSPENDED       600         1653
ORA-01653: unable to extend table TEST.T_RESUMABLE by 128 in tablespace SMALL_TBS
 
SQL> select sid, event from v$session_wait where sid = 137;
 
       SID EVENT
---------- ------------------------------------------------------------
       137 statement suspended, wait error to be cleared
此时告警日志里也会记录一条形如 "statement in resumable session 'User TEST(...)' was suspended due to ..." 的消息。
 
6. 解决空间问题（DBA）
 
sql
SQL> alter database datafile '/oracle/app/oradata/orcl/small01.dbf' resize 100m;
 
Database altered.
7. 见证奇迹
回到第 4 步的那个会话：什么也不用做，插入循环自动继续执行，直到 10000 行全部插完正常结束。DBA_RESUMABLE 中的 
RESUME_TIME 列会记录恢复时刻。如果 600 秒内没有补空间，该会话才会收到 ORA-01653，插入被回滚。

加一道保险：AFTER SUSPEND 触发器

挂起虽然" 静悄悄 " ，但总不能靠人肉轮询 DBA_RESUMABLE 。 Oracle 提供了专门的 AFTER SUSPEND 系统事件触发器，挂起发生的瞬间自动触发，非常适合用来记日志、发告警，甚至自动处理。

SQL> create table resumable_log(
  2    log_time        date,
  3    session_id      number,
  4    err_type        varchar2(30),
  5    object_owner    varchar2(128),
  6    object_type     varchar2(128),
  7    tablespace_name varchar2(128),
  8    object_name     varchar2(128),
  9    error_msg       varchar2(4000)
 10  );
 
Table created.
 
SQL> create or replace trigger trg_after_suspend
  2  after suspend on database
  3  declare
  4    v_err_type varchar2(30);
  5    v_obj_type varchar2(30);
  6    v_owner    varchar2(128);
  7    v_tbs      varchar2(128);
  8    v_obj      varchar2(128);
  9    v_sub_obj  varchar2(128);
 10  begin
 11    if dbms_resumable.space_error_info(
 12         v_err_type, v_obj_type, v_owner, v_tbs, v_obj, v_sub_obj) then
 13      insert into resumable_log(log_time, session_id, err_type, object_owner,
 14                                object_type, tablespace_name, object_name, error_msg)
 15      select sysdate, dr.session_id, v_err_type, v_owner, v_obj_type,
 16             v_tbs, v_obj, dr.error_msg
 17      from dba_resumable dr
 18      where dr.session_id = sys_context('userenv', 'sid');
 19      commit;
 20    end if;
 21  end;
 22  /
 
Trigger created.

关于这个触发器，官方文档说明了一个很关键的性质：触发器里的 SQL 永远是非可恢复的，并且以自治事务方式运行（使用 SYSTEM 回滚段）。也就是说它自己不会再因为空间问题挂起或死锁，可以放心地在里面写日志、发邮件（ UTL_SMTP ），甚至可以在确认无法处理时调用 DBMS_RESUMABLE.ABORT 主动中止，或调用 SET_TIMEOUT 给 DBA 争取更多处理时间。

ITPub博客

Resumable Statement的用法

最新文章