Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 3 4 5 6 7 8 9 | 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.
1 | SQL> alter system kill session '202,3908'; |
