enq: TM – contention

dbtut

5 years ago

Oracle attempts to put a lock on the table with the foreign key if a change is made to the main table with the primary key.

The enq: TM – contention wait event is usually seen during a DML operation on tables with non-indexed foreign key. Or “enq: TM – contention” waiting event can be seen when there is a lock that prevents the operation in the table.

The index must be created for the non-indexed foreign key field for the solution. If there is no foreign key, the session causing the lock should be detected and terminated as follows.

SQL> select a.sid, a.serial#
    from v$session a, v$locked_object b, dba_objects c 
    where b.object_id = c.object_id 
    and a.sid = b.session_id
    and OBJECT_NAME='TABLO_ADI';

   SID    SERIAL#
   ----   --------
   202     3908

SQL> select a.sid, a.serial#

from v$session a, v$locked_object b, dba_objects c

where b.object_id = c.object_id

and a.sid = b.session_id

and OBJECT_NAME='TABLO_ADI';

SID SERIAL#

---- --------

202 3908

The session that causes the lock can be killed as follows.

SQL> alter system kill session '202,3908';

1	SQL> alter system kill session '202,3908';