[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表空间不足。