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 中) 扩展