enq:TT–contention等待事件

客户数据库删除表空间异常，说各种办法都尝试过，我也想好好跟踪下： 删除的时候出现等待事件 enq: TT contention等待事件 本来想跟踪先，看看等待事件问题，结果由于需要要重启数据库，重启后，表空间可以正常删除，感觉略微有点失落， 现对该等待时间做简

客户数据库删除表空间异常，说各种办法都尝试过，我也想好好跟踪下：

删除的时候出现等待事件

enq: TT – contention等待事件

本来想跟踪先，看看等待事件问题，结果由于需要要重启数据库，重启后，表空间可以正常删除，感觉略微有点失落，

现对该等待时间做简单分析：

TT 队列锁在官方文档中介绍为TT, Temporary Table，但是实际在版本8i之后该队列锁更多参与在表空间管理事务中。

也可以称enqueue TT为tablespace lock。

作用

该enqueue TT队列锁用以在各种类型的表空间操作执行过程中避免出现死锁dead lock。

该enqueue lock的 ID2表明正在执行的操作种类，ID1表明该操作对应的表空间号V$Tablespace.TS#。

ID1/ ID2的含义

id1是tablespace number V$Tablespace.TS#，ID2表明执行中的操作类型。以下是操作类型对应代码;

0- 用以避免在drop tablespace和创建rollback segment之间发生死锁

1- 用以串行化在给定表空间上创建数据文件

2- 用以避免在TSPITR tablespace point in time recovery期间发生其他类型操作

4- 用以在创建tablespace时锁住该tablespace id

8- 用以避免在ALTER TABLESPACE期间发生死锁

16- 即16进制的0×10，用以同步 分配和回收盘区，allocation and deallocation of extents.

32+ 用在增加数据文件add datafile和创建表空间时，其中ID2为 32(十进制的)+相对文件号relative file number

最常见的TT enqueue争用是 TT-00000x-0000010 即allocation and deallocation of extents.

如果10g以后遇到类似问题 ， 建议先了解一下extent management、segment management(ASSM、MSSM?)管理方式等，

是否打开了回收站recyclebin功能，以及该表空间上回收站对象的extent数量。

enqueue TT相关的一些BUG列表如下：

Bug 8313598 – ORA-60 on TT enqueue from DBMS_SPACE.ISDATAFILEDROPPABLE_NAME [ID 8313598.8]
Bug 4732503 – Self-deadlock on TT enqueue [ID 4732503.8]
Bug 6809093 – Hang due to TT enqueue waits on an UNDO tablespace [ID 6809093.8]
Bug 14055559 System hang due to TT enqueue contention with BIGFILE tablespace resize
Bug 3833893 SMON may hold the TT enqueue preventing alter of TEMPORARY TABLESPACE
Sessions Hang Due to Self Deadlock on TT Enqueue [ID 948668.1]
Bug 3397983 – Sessions may hang waiting for TT enqueue [ID 3397983.8]
Bug 3467364 – Adding datafiles serializes on the TT enqueue [ID 3467364.8]
Bug 8332021 – Cannot add a datafiles when sessions reporting ORA-1653 / TT enqueue contention between datafile addition and other segment extension operations [ID 8332021.8]
Bug 2272671 – DEADLOCK possible on CREATE INDEX ONLINE (KGL lock v. TT enqueue) [ID 2272671.8]
Bug 6762619 : ENQUEUE “TT” REMAINS WHEN ACCESSING DBA_FREE_SPACE
Bug 6858962 : TT AND US ENQUEUE DEADLOCK
Bug 9439759 : TT ENQUEUE IS NOT RELEASED WHEN CANCEL QUERY
Bug 6265482 : TT ENQUEUE REMAINS AFTER CANCEL “SELECT * FROM DBA_EXTENTS”
Bug 7217723 : ORA-00060 DEADLOCK DETECTED DUE TO TT AND US ENQUEUE
Bug 4732503 : SELF-DEADLOCK TT ENQUEUE ON 9.2.0.7 SIMILAR TO 3425298
Bug 9403168 : WAIT FOR TT-ENQUEUE FOR INSERT TO BASCIFILES DURING ADDING DATAFILE TO SAME TBS
Bug 9451566 : DEADLOCK ON TT-ENQUEUE
Bug 3833893 : SMON GETS “TT ENQUEUE” OF TEMP IF DATEFILE OF TEMP DOESN’T EXIST.
Bug 9948775 : SELF-DEADLOCK ON TT ENQUEUE

The TT portion of the wait event name stands for Temporary Table and typically indicates issues with the temporary tablespace or related storage.

Look for other process that are manipulating temporary tablespaces or tempfiles during the time when these waits were noticed.

Solutions

If an ALTER DATABASE TEMPFILE DROP hangs due to a TT enqueue that is retained by the SMON process in Oracle 9.2.0.6 or lower, Oracle bug 3833893 is responsible. This problem has been solved in Oracle 9.2.0.7. For more information, see Note 867681.

Longer waits for the TT enqueue are generally caused by Oracle bugs. If you experience problems with this enqueue type, you should contact Oracle Support for further assistance.

http://www.itpub.net/forum.php?mod=viewthread&tid=1136919&highlight=【应急解决办法】【有问题不可以过早重启，不然很难跟踪到根本原因】