V$LOCKED_OBJECT - Find Which Tables Are Locked & By Whom

V$LOCKED_OBJECT

Overview

V$LOCKED_OBJECT shows every database object currently locked by an active transaction across all sessions in the instance. Unlike V$LOCK, which deals with raw enqueue metadata (lock types, modes, and IDs), V$LOCKED_OBJECT operates at the logical level — it names the locked object and links it to the Oracle user, OS user, and transaction holding the lock. This view is often the first stop for application support teams investigating “table locked” complaints because it provides immediate human-readable context.

View Type: Dynamic Performance View Available Since: Oracle 8 Required Privileges: SELECT on V_$LOCKED_OBJECT or SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
XIDUSN	NUMBER	Undo segment number of the transaction holding the lock
XIDSLOT	NUMBER	Transaction table slot number within the undo segment
XIDSQN	NUMBER	Sequence number of the transaction — together, XIDUSN, XIDSLOT, XIDSQN uniquely identify the transaction
OBJECT_ID	NUMBER	Object ID of the locked object — join to DBA_OBJECTS for name and type
SESSION_ID	NUMBER	SID of the session holding the lock
ORACLE_USERNAME	VARCHAR2(128)	Oracle database username of the locking session
OS_USER_NAME	VARCHAR2(128)	OS username of the client process
PROCESS	VARCHAR2(128)	OS process ID of the client (client-side PID, not the server SPID)
LOCKED_MODE	NUMBER	Lock mode held (2=Row-Share, 3=Row-Exclusive, 4=Share, 5=Share/Row-X, 6=Exclusive)

Locked Mode Decode

Code	Mode Name	Typical Cause
2	Row Share (RS)	SELECT … FOR UPDATE
3	Row Exclusive (RX)	INSERT, UPDATE, DELETE, MERGE (most common)
4	Share (S)	LOCK TABLE … IN SHARE MODE
5	Share Row Exclusive (SRX)	LOCK TABLE … IN SHARE ROW EXCLUSIVE
6	Exclusive (X)	DDL: ALTER TABLE, DROP TABLE, TRUNCATE, CREATE INDEX

Essential Queries

Basic Usage

-- All currently locked objects with table names and locking session info
SELECT
    lo.session_id                                   AS sid,
    lo.oracle_username                              AS oracle_user,
    lo.os_user_name                                 AS os_user,
    lo.process                                      AS client_pid,
    obj.owner,
    obj.object_name,
    obj.object_type,
    DECODE(lo.locked_mode,
        2, 'Row-Share (RS)',
        3, 'Row-Exclusive (RX)',
        4, 'Share (S)',
        5, 'Share/Row-X (SRX)',
        6, 'Exclusive (X)',
           'Mode-' || lo.locked_mode
    )                                               AS locked_mode,
    lo.xidusn || '.' || lo.xidslot || '.' || lo.xidsqn AS transaction_id
FROM
    v$locked_object lo
    JOIN dba_objects obj ON obj.object_id = lo.object_id
ORDER BY
    obj.object_name,
    lo.session_id;

Monitoring Query

-- Locked objects with full session context and current SQL
SELECT
    lo.session_id                                   AS sid,
    s.serial#,
    lo.oracle_username                              AS oracle_user,
    lo.os_user_name                                 AS os_user,
    s.status,
    s.machine,
    s.module,
    s.program,
    obj.owner,
    obj.object_name,
    obj.object_type,
    DECODE(lo.locked_mode,
        2, 'Row-Share',
        3, 'Row-Exclusive',
        4, 'Share',
        5, 'Share/Row-X',
        6, 'Exclusive',
           'Mode-' || lo.locked_mode
    )                                               AS locked_mode,
    s.seconds_in_wait,
    s.event,
    s.sql_id                                        AS current_sql_id,
    s.prev_sql_id,
    lo.xidusn || '.' || lo.xidslot || '.' || lo.xidsqn AS txn_id,
    TO_CHAR(s.logon_time, 'YYYY-MM-DD HH24:MI:SS') AS logon_time
FROM
    v$locked_object lo
    JOIN dba_objects obj ON obj.object_id = lo.object_id
    JOIN v$session   s   ON s.sid         = lo.session_id
ORDER BY
    obj.object_name,
    s.seconds_in_wait DESC;

Combined with Other Views

-- Lock holder vs waiter analysis: who holds the lock on each object and who is waiting
SELECT
    obj.owner,
    obj.object_name,
    obj.object_type,
    -- Holder
    lo_h.session_id                                 AS holder_sid,
    s_h.username                                    AS holder_user,
    s_h.machine                                     AS holder_machine,
    s_h.status                                      AS holder_status,
    DECODE(lo_h.locked_mode,
        3, 'Row-Exclusive', 4, 'Share',
        5, 'Share/Row-X',   6, 'Exclusive',
           'Mode-' || lo_h.locked_mode
    )                                               AS held_mode,
    -- Waiter (via V$LOCK)
    lw.sid                                          AS waiter_sid,
    s_w.username                                    AS waiter_user,
    s_w.seconds_in_wait                             AS waiter_secs,
    s_w.sql_id                                      AS waiter_sql_id,
    DECODE(lw.request,
        3, 'Row-Exclusive', 4, 'Share',
        5, 'Share/Row-X',   6, 'Exclusive',
           'Mode-' || lw.request
    )                                               AS requested_mode
FROM
    v$locked_object lo_h
    JOIN dba_objects obj ON obj.object_id = lo_h.object_id
    JOIN v$session   s_h ON s_h.sid       = lo_h.session_id
    -- Locate waiters by matching TM lock on the same object
    JOIN v$lock      lw  ON lw.id1        = lo_h.object_id
                        AND lw.type       = 'TM'
                        AND lw.request   != 0
    JOIN v$session   s_w ON s_w.sid       = lw.sid
WHERE
    lo_h.locked_mode >= 3       -- sessions actively modifying (not just RS)
ORDER BY
    s_w.seconds_in_wait DESC;

Advanced Analysis

-- DML lock analysis: aggregate lock counts per object to identify hot tables
SELECT
    obj.owner,
    obj.object_name,
    obj.object_type,
    COUNT(DISTINCT lo.session_id)                   AS locking_sessions,
    COUNT(DISTINCT lo.xidusn || '.' || lo.xidslot || '.' || lo.xidsqn)
                                                    AS distinct_transactions,
    SUM(CASE WHEN lo.locked_mode = 3 THEN 1 ELSE 0 END) AS row_exclusive_cnt,
    SUM(CASE WHEN lo.locked_mode = 4 THEN 1 ELSE 0 END) AS share_cnt,
    SUM(CASE WHEN lo.locked_mode = 6 THEN 1 ELSE 0 END) AS exclusive_cnt,
    MIN(s.logon_time)                               AS oldest_lock_session_logon
FROM
    v$locked_object lo
    JOIN dba_objects obj ON obj.object_id = lo.object_id
    JOIN v$session   s   ON s.sid         = lo.session_id
GROUP BY
    obj.owner,
    obj.object_name,
    obj.object_type
ORDER BY
    locking_sessions DESC,
    obj.object_name;

-- Transaction-level view: all objects locked by a single transaction
-- (Replace &sid and &serial with the target session values)
SELECT
    lo.xidusn || '.' || lo.xidslot || '.' || lo.xidsqn AS transaction_id,
    lo.session_id                                   AS sid,
    s.serial#,
    s.username,
    s.status,
    s.seconds_in_wait,
    s.event,
    obj.owner,
    obj.object_name,
    obj.object_type,
    DECODE(lo.locked_mode,
        2, 'Row-Share',
        3, 'Row-Exclusive',
        4, 'Share',
        5, 'Share/Row-X',
        6, 'Exclusive',
           'Mode-' || lo.locked_mode
    )                                               AS locked_mode
FROM
    v$locked_object lo
    JOIN dba_objects obj ON obj.object_id = lo.object_id
    JOIN v$session   s   ON s.sid         = lo.session_id
WHERE
    lo.session_id = &sid
    AND s.serial# = &serial
ORDER BY
    obj.object_name;

-- Exclusive locks only: DDL operations or explicit LOCK TABLE commands
SELECT
    lo.session_id                                   AS sid,
    s.serial#,
    lo.oracle_username                              AS oracle_user,
    lo.os_user_name                                 AS os_user,
    s.machine,
    s.program,
    s.module,
    obj.owner,
    obj.object_name,
    obj.object_type,
    'Exclusive (Mode 6)'                            AS locked_mode,
    s.seconds_in_wait,
    s.event,
    s.sql_id,
    TO_CHAR(s.logon_time, 'YYYY-MM-DD HH24:MI:SS') AS logon_time
FROM
    v$locked_object lo
    JOIN dba_objects obj ON obj.object_id = lo.object_id
    JOIN v$session   s   ON s.sid         = lo.session_id
WHERE
    lo.locked_mode = 6
ORDER BY
    s.seconds_in_wait DESC;

Common Use Cases

• Immediate “who locked what” diagnosis — When application users report that a DML operation is hanging, this view immediately shows which session holds a Row-Exclusive lock on the target table and identifies the OS user and client machine.
• DDL failure diagnosis — When an ALTER TABLE or CREATE INDEX fails with ORA-00054 (Resource Busy), use this view filtered on locked_mode = 6 to find the exclusive lock holder.
• Transaction accountability — The XIDUSN, XIDSLOT, XIDSQN combination uniquely identifies the transaction. Use it to join to V$TRANSACTION for undo usage, start SCN, and commit status.
• Multi-object transaction auditing — A session performing a complex batch update may hold Row-Exclusive locks on dozens of tables simultaneously. This view exposes all of them at once.
• Stale lock detection — Sessions with ORACLE_USERNAME populated but STATUS = ‘INACTIVE’ in V$SESSION are holding locks from a transaction that completed its last SQL but never committed or rolled back.
• Application module attribution — Joining to V$SESSION for MODULE and ACTION identifies which application component is responsible for long-held locks, enabling targeted application-side fixes.

Related Views

• V$LOCK — Raw enqueue lock data; essential companion for understanding the exact lock type (TX vs TM) and whether sessions are blocked
• V$SESSION — Join on SESSION_ID for full session context including username, machine, program, status, and current SQL
• V$TRANSACTION — Join on XIDUSN / XIDSLOT / XIDSQN for undo usage, start time, and commit/rollback status of the locking transaction
• DBA_OBJECTS — Join on OBJECT_ID to resolve object name, type, and owner for the locked object
• V$ACTIVE_SESSION_HISTORY — Historical lock holder/waiter data captured via BLOCKING_SESSION sampling, useful when the lock has already been released

Version Notes

• Oracle 8: View introduced. Initial columns covered the basic locked object and session relationship.
• Oracle 10g: Integration with ASH improved lock visibility. ORACLE_USERNAME and OS_USER_NAME reflect current session values at lock acquisition time.
• Oracle 11g: No major structural changes. The PROCESS column represents the client-side OS PID (from the client machine), which differs from the server-side SPID in V$PROCESS.
• Oracle 12c: CON_ID column added for multitenant environments. When queried from CDB$ROOT with appropriate privileges, locks across all PDBs are visible.
• Oracle 19c: Automatic indexing (AI) DDL operations acquire exclusive locks visible in this view. The oracle_username for AI operations is typically SYS.
• Oracle 21c / 23ai: In 23ai environments, JSON Relational Duality view write operations acquire Row-Exclusive locks on the underlying base tables, which appear here. Blockchain Table locks show up with locked_mode = 3 for DML and cannot be bypassed by DDL.