[20160325]参数resumable_timeout.txt

最新推荐文章于 2022-02-20 18:06:40 发布

cuixun7780

最新推荐文章于 2022-02-20 18:06:40 发布

阅读量92

点赞数

[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.