Sessions Hang Due to Self Deadlock on TT Enqueue (Doc ID 948668.1)

Last updated on OCTOBER 07, 2009

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4
This problem can occur on any platform.

Symptoms

A session can hang waiting on TT enqueue while performing maintenance activities related to tablespaces/ objects on a tablespace.

Querying GV$LOCK shows that the holder of the TT enqueue is in self-deadlock on the same enqueue.

The self-deadlocked process was executing the below SQL issued from Enterprise Manager (EM).

BEGIN DBMS_SPACE.ISDATAFILEDROPPABLE_NAME(:1,:2); END;

 

EXAMPLE:

 

SQL> SELECT DECODE(request,0,'Holder: ','Waiter: ') || sid sid, id1, id2, lmode, request, type, inst_id FROM GV$LOCK WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM GV$LOCK WHERE request > 0) ORDER BY id1, request;

SID
------------------------------------------------------------------------
ID1 ID2 LMODE REQUEST TY INST_ID
---------- ---------- ---------- ---------- -- ----------
Holder: 775 <<==============
217 16 6 0 TT 4

Waiter: 874
217 16 0 4 TT 1

Waiter: 775 <<==============
217 16 0 4 TT 4

Session 775 is in self-deadlock over TT enqueue.
Querying V$SQL confirms the SQL as
BEGIN DBMS_SPACE.ISDATAFILEDROPPABLE_NAME(:1,:2); END;

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms