Oracle Enqueues Wait Events 二

enq: TM – contention 原理即解决方法 enq: TM – contention 等待事件通常是由于正在DML操作的的表上的外键约束失效导致。一旦我们找到问题的根源，我们就可以为那些使用外键的表添加外键约束来解决该问题。(原文：The enq: TM – contention event is usually due to missing foreign key constraints on a table that’s part of an Oracle DML operation. Once you fix the problem by adding the foreign key constraint to the relevant table, the enq: TM – contention event will go away.) enq: TM – contention等待事件会话有可能是由于正在执行的insert 语句的表的外键没有相关约束，即当引用父表的依赖表或子表的外键约束缺少关联键上的索引时，会发生这种情况，同时还会当Oracle对父表中由子表的外键引用的主键列执行修改，它还将获得子表的表级锁。(The waits on the enq: TM – contention event for the sessions that are waiting to perform insert operations are almost always due to an unindexed foreign key constraint.. This happens when a dependent or child table’s foreign key constraint that references a parent table is missing an index on the associated key. Oracle acquires a table lock on a child table if it’s performing modifications on the primary key column in the parent table that’s referenced by the foreign key of the child table. ) 它对父表中由子表的外键引用的主键列执行修改。注意，这些是全表锁(TM)，而不是行级锁(TX)——因此，这些锁不是某一行，而是对于整个表。自然，一旦获得了这个表锁，Oracle将阻止所有其他试图修改子表数据的会话。一旦在引用父表的列作为子表的外键列上创建索引，那么TM争用而引起的等待就会消失。(原文：it’s performing modifications on the primary key column in the parent table that’s referenced by the foreign key of the child table.Note that these are full table locks (TM), and not row-level locks (TX)—thus, these locks aren’t restricted to a row but to the entire table. Naturally, once this table lock is acquired, Oracle will block all other sessions that seek to modify the child table’s data. Once you create an index in the child table performing on the column that references the parent table, the waits due to the TM contention will go away.) 如果在添加索引的时候报ORA-00054，那么就需要我们确认当前那个会话阻塞了，和业务确认后把会话kill，再执行：添加索引有可能的报错：ORA-00054: resource busy and acquire with NOWAIT specified确认当前阻塞会话信息：（11G-19C）select s.inst_id inst, s.sid, s.serial#, s.username, substr(s.program,1,15) program,  s.sql_id, s.event,machine,OSUSER,round(last_call_et/60,2) lmins ,s.seq#,module  from gv$session s where s.type <> 'BACKGROUND'   and (s.event not like 'SQL*Net mes%' OR S.event LIKE '%dblink%' ) and s.event != 'reliable message'     and s.wait_class <> 'Idle' order by inst_id, s.sql_id, s.inst_id, s.program;