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
The session that causes the lock can be killed as follows.
SQL> alter system kill session '202,3908';