ITPub博客

首页 > 数据库 > Oracle > [20160325]参数resumable_timeout.txt

[20160325]参数resumable_timeout.txt

原创 Oracle 作者:lfree 时间:2016-03-25 11:14:59 0 删除 编辑

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2063600/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2468
  • 访问量
    6276887