[20160325]参数resumable_timeout.txt
--昨天测试环境遇到library cache lock的情况，主要测试磁盘空间很紧张，但是设置了参数resumable_timeout。
--开发通过ctas建立表时，空间不够挂起，估计他程序挂起异常关闭，ctas依旧在后台运行。但是访问到这个表的程序全部挂起。
--当时并没有太注意statement suspended, wait error to be cleared等待事件，今天看看。
1.环境：
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
CREATE TABLESPACE tea DATAFILE
'/mnt/ramdisk/book/tea01.dbf' SIZE 1536K AUTOEXTEND OFF
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
SCOTT@book> alter user scott quota unlimited on tea;
User altered.
SCOTT@book> alter session set resumable_timeout=3600 ;
Session altered.
SCOTT@book> create table t1 tablespace tea as select * from dba_objects ;
...
-- 由于我限制表空间tea大小，加上参数resumable_timeout，操作会暂时挂起，等待空间分配。
SCOTT@book> @ &r/wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ------------------- --------------- ---------------
0000000062657100 0000000000000001 00 1650815232 1 0 80 1789 25 SQL*Net message to client WAITED SHORT TIME 2 0
00 00 00 0 0 0 68 1745 105 statement suspended, wait error to be cl WAITING 32617 0
eared
--仅仅知道sid。
SCOTT@book> @ &r/ev_name.sql 'statement suspended'
EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------- ---------------------------------------- -------------------- -------------------- -------------------- ------------- ----------- --------------------
248 680822103 statement suspended, wait error to be cl 3290255840 2 Configuration
eared
SCOTT@book> @ &r/pt 'select * from dba_resumable'
old 10: passing xmltype(cursor( &1 ))
new 10: passing xmltype(cursor( select * from dba_resumable ))
ROW_NUM COL_NAME COL_VALUE
---------- ------------------------------ -----------------------------------------------------------------------
1 USER_ID 83
SESSION_ID 68
INSTANCE_ID 1
STATUS SUSPENDED
TIMEOUT 3600
START_TIME 03/25/16 11:06:55
SUSPEND_TIME 03/25/16 11:06:56
NAME User SCOTT(83), Session 68, Instance 1
SQL_TEXT create table t1 tabl
ERROR_NUMBER 1652
ERROR_PARAMETER1 8
ERROR_PARAMETER2 TEA
ERROR_MSG ORA-01652: unable to extend temp segment by 8 in tablespace TEA
13 rows selected.
--已经提示很明确了，虽然看到SQL_TEXT不全。就是TEA表空间不足。