orasupp

58 积分	0 好友	0 主题

发消息

drop 临时表空间问题

1^#

发表于 2012-4-24 14:48:08 | 查看: 6255| 回复: 4

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

出现enqueue
Id1 Id2 INSTANCE_ID Blocker B Mode B Req B Type INSTANCE_ID Waiter W Mode W Req W Type
3 2 2 2194,1 Row Exclusive NONE TS 1 2035,2244 NONE Exclusive TS

Row Exclusive lock,session 2194 是SMON 进程.

drop 了好久,都不行,SMON是正在清理临时断吗?这种情况怎么办呢?附上TARCE文件

trace.zip

13.23 KB, 下载次数: 835

分享0

收藏0 回复只看该作者道具举报

orasupp

5^#

发表于 2012-4-25 14:59:30

OK........谢谢

回复只看该作者道具举报

Maclean Liu(刘相兵

4^#

发表于 2012-4-24 21:34:03

enq: TS - contention

TS Serializes accesses to temp segments

建议在drop temp tablespace 之前确认没有active session在做disk sort之类使用 temp segment的活动

回复只看该作者道具举报

Maclean Liu(刘相兵

3^#

发表于 2012-4-24 21:29:05

PARSING IN CURSOR #6 len=90 dep=1 uid=0 oct=3 lid=0 tim=1303953364483068 hv=3149739229 ad='5513dd80'
select obj#, type#, flags, related, bo, purgeobj, con# from RecycleBin$ where ts#=:1
END OF STMT
PARSE #6:c=0,e=125,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1303953364483067
BINDS #6:
kkscoacd
Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=2a978743d0  bln=22  avl=02  flg=05
  value=3
EXEC #6:c=0,e=383,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1303953364483491
FETCH #6:c=0,e=7,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1303953364483513
STAT #6 id=1 cnt=0 pid=0 pos=1 obj=702 op='TABLE ACCESS BY INDEX ROWID RECYCLEBIN$ (cr=2 pr=0 pw=0 time=8 us)'
STAT #6 id=2 cnt=0 pid=1 pos=1 obj=704 op='INDEX RANGE SCAN RECYCLEBIN$_TS (cr=2 pr=0 pw=0 time=6 us)'
WAIT #1: nam='DFS lock handle' ela= 299 type|mode=1128857605 id1=13 id2=1 obj#=-1 tim=1303953364483950
WAIT #1: nam='DFS lock handle' ela= 239 type|mode=1128857605 id1=13 id2=3 obj#=-1 tim=1303953364484233
WAIT #1: nam='DFS lock handle' ela= 1101 type|mode=1128857605 id1=13 id2=2 obj#=-1 tim=1303953364485397
WAIT #1: nam='enq: TS - contention' ela= 237 name|mode=1414725638 tablespace ID=3 dba=1 obj#=-1 tim=1303953364485747
WAIT #1: nam='enq: TS - contention' ela= 488714 name|mode=1414725638 tablespace ID=3 dba=2 obj#=-1 tim=1303953364974529
WAIT #1: nam='enq: TS - contention' ela= 489408 name|mode=1414725638 tablespace ID=3 dba=2 obj#=-1 tim=1303953365463985

回复只看该作者道具举报

orasupp

2^#

发表于 2012-4-24 15:58:14

找到原因了,RAC上其他NODE有 INACTIVE 的SESSION,直接KILL就能DROP了

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册