drop 临时表空间问题

orasupp

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文件

orasupp

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

Maclean Liu(刘相兵

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

Maclean Liu(刘相兵

enq: TS - contention

TS         Serializes accesses to temp segments

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

orasupp

OK........谢谢