参考:DROP Tablespace Hangs when the datafile header is corrupted (文档 ID 1529267.1)
公司同事在RAC上创建创建表空间数据文件误建到了本地系统,尝试删除,发现语句一直在等待中...
收集ASH报告,AWR报告,发现等待事件
enq: TT - contention
DROP TABLESPACE session was waiting for TT enqueue.
V$LOCK shows many TT locks hold by same session sid=4569.
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ------ ------ --- --- ----- ------- ------ -----
0000000479C08590 0000000479C085E8 4569 TT 31 0 4 0 132585 0
0000000479C0DBF8 0000000479C0DC50 4569 TT 31 0 4 0 132553 0
0000000479C0E218 0000000479C0E270 4569 TT 31 0 4 0 134884 0
SQL> select sid,pid,spid,s.username,s.program from v$session s,v$process p where s.paddr=p.addr and s.sid=4569;
SID PID SPID USERNAME PROGRAM
---------- ---------- ------------- --------------- ---------------------------------------
4569 177 17997 BMC sqlplus@chpdat-pl2 (TNS V1-V3)
BMC it is a monitoring tool.
处理也比较简单,Patrol关闭,重新执行DROP语句即可。