Skip to content
DBAExperts
Oracle Diagnostics

How to view and resolve session locks in Oracle

A lock happens when one session waits on a resource another one holds. Identifying who is blocking whom is the first step.

  1. 1

    Identify blocked sessions

    v$session shows directly which session is blocked and by which, through blocking_session.

    SELECT sid, serial#, username, blocking_session, event, seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL;
  2. 2

    Review the lock chain

    When several sessions are chained together, this query shows the full hierarchy.

    SELECT * FROM v$session
WHERE sid IN (
  SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL
);
  3. 3

    Find out which SQL holds the lock

    Join the blocking session with its active SQL to understand the cause.

    SELECT s.sid, s.serial#, q.sql_text
FROM v$session s
JOIN v$sql q ON s.sql_id = q.sql_id
WHERE s.sid = &sid_bloqueadora;
  4. 4

    Kill the session only if necessary

    As a last resort, terminate the blocking session. This rolls back its transaction; confirm with the process owner first.

    ALTER SYSTEM KILL SESSION '145,28' IMMEDIATE;

// common mistake

Killing the wrong session rolls back valid work and does not fix the cause. Many locks come from a forgotten COMMIT in the application, not from the database.

// when it's worth an expert

If locks are recurring or affecting operations, you need to attack the root cause in the code or the design. At dba.mx we diagnose and resolve contention at a fixed price.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.