oracle删除临时表空间怎么办,删除临时表空间hang处理

今天在本机数据库做测试的时候当删除临时表空间时hang住了，等了好久没见反应，

后面果断kill退出会话重来删除成功。经分析遭遇Bug 15913577，真是够背，欲知详细，请容我一一道来。

1、删除表空间，一直处于等待状态，最后不得以ctrl+c：

SQL> alter database default  temporary tablespace temp1;

Database altered.

SQL> drop  tablespace temp including contents and datafiles;

drop  tablespace temp including contents and datafiles

*

ERROR at line 1:

ORA-01013: user requested cancel of current operation

2、查看进程hang的等待事件如下：

11gdb[/home/oracle]$ps -ef |grep LOCAL=YES

oracle   24109 24108  0 15:37 ?        00:00:01 oracleora11gdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

oracle   24257 24182  0 16:03 pts/1    00:00:00 grep LOCAL=YES

SQL> select sid,sql_id,status,event from v$session where paddr = (select addr from v$process where spid = 24109);

SID SQL_ID        STATUS   EVENT

---------- ------------- -------- ----------------------------------------------------------------

1 63yna2jqw03x0 ACTIVE   enq: TS - contention

3、做了下systemdump如下：

SQL> oradebug setospid 24109

Oracle pid: 27, Unix process pid: 24109, image: oracle@11gdb (TNS V1-V3)

SQL>  oradebug unlimit

Statement processed.

SQL> oradebug dump systemstate 258

Statement processed.

SQL>  oradebug dump systemstate 258

Statement processed.

SQL> exit

4、继续分析dump出来的trace文件

----------------------------------------

SO: 0xa6930740, type: 4, owner: 0xa9a861d0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3

proc=0xa9a861d0, name=session, file=ksu.h LINE:12729, pg=0

(session) sid: 1 ser: 19 trans: 0x7268d430, creator: 0xa9a861d0

flags: (0x8100041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-

flags2: (0x48009) -/DDLT2/INC

DID: , short-term DID:

txn branch: (nil)

edition#: 100              oct: 41, prv: 0, sql: 0x2b596c77fdf0, psql: 0xab2eaf78, user: 0/SYS

ksuxds FALSE at location: 0

service name: SYS$USERS

client details:

O/S info: user: oracle, term: pts/0, ospid: 24108

machine: 11gdb program: sqlplus@11gdb (TNS V1-V3)

application name: sqlplus@11gdb (TNS V1-V3), hash value=2572252297

Current Wait Stack:

0: waiting for 'enq: TS - contention'                       <<=============当前等待'enq: TS - contention'

name|mode=0x54530006, tablespace ID=0x3, dba=0x1

wait_id=412 seq_num=413 snap_id=1

wait times: snap=16 min 39 sec, exc=16 min 39 sec, total=16 min 39 sec

wait times: max=infinite, heur=16 min 39 sec

wait counts: calls=334 os=334

in_wait=1 iflags=0x15a0

There is at least one session blocking this session.

Dumping 1 direct blocker(s):

inst: 1, sid: 13, ser: 1                     <<=============堵塞源为sid: 13

Dumping final blocker:

inst: 1, sid: 13, ser: 1

Wait State:

fixed_waits=0 flags=0x2b boundary=0xa6932628/0

Session Wait History:

elapsed time of 23586231 min 16 sec since current wait

0: waited for 'ksdxexeotherwait'

=0x0, =0x0, =0x0

wait_id=436 seq_num=437 snap_id=1

wait times: snap=0.021888 sec, exc=0.021888 sec, total=0.021888 sec

wait times: max=30.000000 sec

wait counts: calls=0 os=0

occurred after 0.000000 sec of elapsed time

1: waited for 'ksdxexeotherwait'

=0x0, =0x0, =0x0

wait_id=435 seq_num=436 snap_id=1

wait times: snap=0.025816 sec, exc=0.025816 sec, total=0.025816 sec

wait times: max=30.000000 sec

wait counts: calls=0 os=0

occurred after 0.000000 sec of elapsed time

。。。。。。。。。。

Process Group: DEFAULT, pseudo proc: 0xa836fac8

O/S info: user: oracle, term: UNKNOWN, ospid: 24109

OSD pid info: Unix process pid: 24109, image: oracle@11gdb (TNS V1-V3)

Short stack dump:

ksedsts()+465

分析：会话当前等待'enq: TS - contention'，且call stack中有函数ktsttdrop，符合Bug 15913577，

该BUG影响12.2以下的所有系统平台的数据库。

5、查看SID: 13对应的进程是什么

SQL> select spid from v$process where addr = (

2   select paddr from v$session where sid = 13);

SPID

------------------------

23843

SQL> !ps -ef |grep 23843

oracle   23843     1  0 14:41 ?        00:00:00 ora_smon_ora11gdb

oracle   24596 24422  0 17:49 pts/1    00:00:00 /bin/bash -c ps -ef |grep 23843

分析总结：

SMON进程堵了删除临时表空间会话，会话当前等待'enq: TS - contention'，且call stack中有函数ktsttdrop，符合Bug 15913577，

采取临时措施，重新sqlplus在删除表空间成功。

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