使用可恢复性语句功能

1.常规情况，遇到空间不足时，SQL语句会在执行时报错，中断执行

SQL> create table e tablespace t2m as select * from emp;

Table created.

SQL> insert into e select * from e;

14 rows created.

7168 rows created.

SQL> /
insert into e select * from e
*
ERROR at line 1:
ORA-01653: unable to extend table SCOTT.E by 128 in tablespace T2M

2.启用可恢复性语句
conn / as sysdba
授权
SQL> grant execute on dbms_resumable to scott;

Grant succeeded.

SQL> grant resumable to scott;

Grant succeeded.

SQL> grant select on v_$mystat to scott;

Grant succeeded.

创建实验用表空间，足够小最好
SQL> create tablespace t2m datafile '/u01/app/oracle/oradata/orcl/t2m.dbf' size 1m autoextend off;

Tablespace created.

conn scott/tiger
确认scott会话的ID
SQL> select sid from v$mystat where rownum=1;

       SID
----------
       191

打开可恢复性语句功能，并设置等待时间为3600s
SQL> alter session enable resumable timeout 3600;

Session altered.

可以通过dbms_resumable查看当前我们设置的等待时间
SQL> select dbms_resumable.get_session_timeout(191) from dual;

DBMS_RESUMABLE.GET_SESSION_TIMEOUT(191)
---------------------------------------
                                   3600

创建实验表
SQL> create table e tablespace t2m as select * from emp;

Table created.

SQL> insert into e select * from e;

14 rows created.

SQL> /

7168 rows created.

SQL> /
挂起了

SQL> /

14336 rows created.

SQL> commit;

Commit complete.

再开一个SYS窗口
conn sys/oracle

查看191号会话，在发生什么事件
SQL> select event from v$session where sid=191;

EVENT
----------------------------------------------------------------
statement suspended, wait error to be cleared
语句被挂起，直到错误被消除

啥错误？
SQL> select session_id,error_msg from dba_resumable where session_id=191;

SESSION_ID
----------
ERROR_MSG
--------------------------------------------------------------------------------
       191
ORA-01653: unable to extend table SCOTT.E by 8 in tablespace T2M
表空间不足

扩展表空间
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/t2m.dbf' resize 4m;

Database altered.

默认挂起时间是7200s
SQL> select timeout from dba_resumable;

   TIMEOUT
----------
      7200

SQL> select timeout,status,sql_text,error_msg from dba_resumable where session_id=46;

TIMEOUT  STATUS  SQL_TEXT   ERROR_MSG
20000 SUSPENDED
insert into test select * from test
ORA-01653: 表 SCOTT.TEST 无法通过 8 (在表空间 TEST 中) 扩展