Oracle problems

Oracle Blocking Sessions: Find the Root Blocker, Decide Whether to Kill It, and See the Pile-Up Coming

2 November 2026

Twenty sessions wait on enq: TX - row lock contention, the application has timed out, and the phone rings. The chain of who waits for whom can be ten sessions long, but it always ends at one session, and that one is usually doing nothing at all.

Find the root in one query

Since 11g Release 2, V$SESSION carries FINAL_BLOCKING_SESSION: the session at the end of the chain, not the next one up. There is no need to walk the chain yourself.

SELECT s.final_blocking_instance              AS inst,
       s.final_blocking_session               AS sid,
       COUNT(*)                               AS blocked_sessions,
       ROUND(MAX(s.wait_time_micro) / 1e6)    AS longest_wait_s,
       MIN(s.event)                           AS wait_event
FROM   gv$session s
WHERE  s.final_blocking_session IS NOT NULL
AND    s.final_blocking_session_status = 'VALID'
GROUP  BY s.final_blocking_instance, s.final_blocking_session
ORDER  BY blocked_sessions DESC;

One row with 20 blocked sessions is one problem. Five rows with four each is a different one: a hot row or a hot block that many sessions fight over, and killing one blocker only promotes the next.

What the root blocker is doing

SELECT s.inst_id, s.sid, s.serial#, s.username, s.osuser, s.machine,
       s.program, s.module, s.status, s.last_call_et, s.event,
       s.sql_id, s.prev_sql_id, t.start_date, t.used_urec
FROM   gv$session s
LEFT JOIN gv$transaction t ON t.inst_id = s.inst_id
                          AND t.addr    = s.taddr
WHERE  (s.inst_id, s.sid) IN
       (SELECT final_blocking_instance, final_blocking_session
        FROM   gv$session
        WHERE  final_blocking_session IS NOT NULL);

The row answers the only question that matters before you kill anything:

• STATUS INACTIVE, event SQL*Net message from client, an open transaction, LAST_CALL_ET in the thousands. The session changed rows and never committed: a user who went to lunch with a form open, a middle tier that lost the connection, a job that errored between the UPDATE and the COMMIT. Nothing will release the lock until the session commits, rolls back or dies. This is the common case.
• STATUS ACTIVE on a long statement. A batch that holds the rows while it works. Killing it rolls back everything it did, which can take longer than letting it finish. Look at USED_UREC before you decide.
• Itself waiting on something else. A final blocker that waits on log file sync, a full TEMP or a slow read is not the cause but the first victim of another problem. Fix that one.

The row that is locked, when you need it:

SELECT w.inst_id, w.sid, o.owner, o.object_name, o.object_type,
       w.row_wait_file#, w.row_wait_block#, w.row_wait_row#
FROM   gv$session w
JOIN   dba_objects o ON o.object_id = w.row_wait_obj#
WHERE  w.final_blocking_session IS NOT NULL
AND    w.row_wait_obj# > 0;

What the wait event says

The TM case is worth a query of its own, because it comes back every time the parent table is cleaned up. Foreign keys whose first column has no index to lead it:

SELECT c.owner, c.table_name, c.constraint_name, cc.column_name
FROM   dba_constraints c
JOIN   dba_cons_columns cc ON cc.owner = c.owner
                          AND cc.constraint_name = c.constraint_name
                          AND cc.position = 1
WHERE  c.constraint_type = 'R'
AND    c.owner = 'APP'
AND    NOT EXISTS (SELECT 1
                   FROM   dba_ind_columns ic
                   WHERE  ic.table_owner = c.owner
                   AND    ic.table_name  = c.table_name
                   AND    ic.column_name = cc.column_name
                   AND    ic.column_position = 1)
ORDER  BY c.owner, c.table_name;

Kill it, or not

1. An idle session with an open transaction, older than the application's own timeout: kill it. Its uncommitted changes roll back, its committed ones stay. ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id' IMMEDIATE;. Tell the owner afterwards; OSUSER, MACHINE and MODULE say who.
2. An active batch everyone depends on: let it finish, or ask its owner. A rollback of a million rows takes as long as the million rows took.
3. Many small chains on one table: the fix is in the design, not in a kill. An unindexed foreign key, a counter row that every transaction updates, a sequence replaced by SELECT MAX(id) + 1.

Killing the session does not always free the lock at once: a large transaction rolls back first, and the waiters wait for the rollback.

ORA-00060: the deadlock

Two sessions each hold what the other wants. Oracle sees it within seconds and rolls back one statement, not the transaction, with ORA-00060: deadlock detected while waiting for resource. The session that got the error still holds its other locks and must roll back or retry. The alert log names a trace file with both statements and both sessions. Count them from SQL:

SELECT COUNT(*) AS deadlocks_24h
FROM   v$diag_alert_ext
WHERE  message_text LIKE '%ORA-00060%'
AND    originating_timestamp > SYSTIMESTAMP - 1;

A deadlock is nearly always one of two things: two transactions that change the same two rows in opposite orders, or the unindexed foreign key from the table above, where a table lock meets a row lock. The first is fixed in the application, the second with an index.

See it coming

A blocking chain grows for minutes before the first call: one blocked session, then four, then twenty. Radar Engine, the detection layer of Scops, watches the shape, and a blocking chain is one of the urgent Critical findings it mails at once:

On the Live Activity page of Scops, the Blocked filter shows the chain as it is now, and the blocker can be killed from there. And because Scops samples the sessions of every database every 5 seconds, the chain is also there after the fact: who blocked whom at 02:14, on which statement, for how long, with or without the Diagnostics Pack. With the OS credential of the database, the log detectors report ORA-00060 from the alert log as well.

Questions

Why is the blocker idle? Nothing is running.
An idle session can hold locks: it changed rows and has not committed. STATUS is INACTIVE, the event is SQL*Net message from client, and V$TRANSACTION has a row for it. Only a commit, a rollback or the end of the session releases the rows.

Does killing the blocker lose data?
Its uncommitted changes roll back; everything it committed stays. The rollback can take a while for a large transaction, and the waiters wait for it.

I get ORA-00060 and the DBA says nothing is wrong. Who is right?
Both. Oracle resolved the deadlock by rolling back one statement, so the database is fine, but the application has to handle the error and retry. Repeated deadlocks point at a missing foreign key index or two transactions that take rows in opposite orders.

What is enq: TM - contention?
A table-level lock. The usual cause is a foreign key on a child table without an index on its columns: a delete or an update of the parent key then locks the whole child table.

Related problems

• Undo tablespace full: ORA-30036
• ORA-00020: maximum number of processes
• Scheduler job failed, broken or stuck
• RAC lost blocks: gc cr block lost
• enq: TX - row lock contention
• enq: TM - contention
• All Oracle problems

Sources

• Oracle: ORA-00060
• Oracle: V$SESSION, V$DIAG_ALERT_EXT
• Oracle: Data Concurrency and Consistency
• Scops docs: Detector reference, Live Activity