Identify Locked Objects in Oracle Database

calendar Sep 3, 2024 / · 16 min read · Oracle Database Administration SQL v$locked_object dba_objects v$lock v$session  ·
Share on: linkedin copy

Identify Locked Objects in Oracle Database using v$locked_object, dba_objects, v$lock and v$session

Purpose

A developer reports that an INSERT that should finish in seconds is still running after ten minutes. No error has come back to the application, no timeout has fired, and nothing is moving. In the vast majority of such cases the root cause is a database lock: a session with an uncommitted DML transaction is holding a row or table lock, and the waiting session cannot proceed until that lock is released. The four-view join below makes the diagnosis immediate.

V$LOCKED_OBJECT contains a row for every object held under an active lock and identifies the session responsible. DBA_OBJECTS resolves the numeric object IDs stored in that view into human-readable schema and object names. V$LOCK adds the lock mode — expressed as an integer that the query decodes into labels such as Row-X (SX) or Exclusive — and a BLOCK flag that indicates whether the holding session is actively preventing other sessions from progressing. V$SESSION supplies the Oracle and operating-system usernames for each session, plus the SID,SERIAL# pair that is the argument to ALTER SYSTEM KILL SESSION if the application cannot recover on its own.

The join of all four views takes fractions of a second and reads only dynamic performance structures. It is safe to run repeatedly on a busy production database. The result set names what is locked, who holds it, and whether the situation is actively blocking other work — all in a single pass.

Code

 1set lines 100 pages 999
 2col username    format a20
 3col sess_id     format a10
 4col object      format a25
 5col mode_held   format a10
 6
 7select  oracle_username || ' (' || s.osuser || ')' username
 8,       s.sid || ',' || s.serial# sess_id
 9,       owner || '.' || object_name object
10,       object_type
11,       decode( l.block
12        ,       0, 'Not Blocking'
13        ,       1, 'Blocking'
14        ,       2, 'Global') status
15,       decode(v.locked_mode
16        ,       0, 'None'
17        ,       1, 'Null'
18        ,       2, 'Row-S (SS)'
19        ,       3, 'Row-X (SX)'
20        ,       4, 'Share'
21        ,       5, 'S/Row-X (SSX)'
22        ,       6, 'Exclusive', TO_CHAR(lmode)) mode_held
23from    v$locked_object v
24,       dba_objects d
25,       v$lock l
26,       v$session s
27where   v.object_id = d.object_id
28and     v.object_id = l.id1
29and     v.session_id = s.sid
30order by oracle_username
31,       session_id
32/

Code Breakdown

Display setup

SET LINES 100 PAGES 999 adjusts SQL*Plus output formatting. A line width of 100 characters prevents the columns from wrapping mid-value; setting pages to 999 stops the column headers from repeating after every 24 rows in a long result set. The four COL FORMAT commands then allocate fixed character widths to each output column.

  • col username format a20 — space for the combined Oracle and OS username. Shops with long schema names or service-account names should increase this to a30.
  • col sess_id format a10 — sufficient for standard SID,SERIAL# values. Very large serial numbers may require a12.
  • col object format a25 — for owner.object_name. Objects with longer names may truncate; adjust to a35 if needed.
  • col mode_held format a10 — accommodates the longest decode result, S/Row-X (SSX). Increase to a14 if values appear cut off.

The SELECT clause in detail

oracle_username || ' (' || s.osuser || ')' username

ORACLE_USERNAME is the database login account — the schema name. OSUSER from V$SESSION is the operating-system account that opened the connection. For JDBC connection pools the OS user is typically a service account; for interactive SQL*Plus or IDE sessions it is the developer's or DBA's workstation login. Combining them in one column makes it immediately clear whether the locking session is an application process or a person sitting at a terminal who forgot to commit.

s.sid || ',' || s.serial# sess_id

SID is the session identifier. It uniquely marks an active session but is recycled when a session disconnects and a new connection takes the same slot. SERIAL# increments each time a SID is reused. The pair SID,SERIAL# is unambiguous — it names one specific session in history — and it is the exact argument to ALTER SYSTEM KILL SESSION 'sid,serial#'. Never use SID alone; you risk targeting a different session that has taken the slot since the original disconnected.

owner || '.' || object_name object

V$LOCKED_OBJECT stores only a numeric OBJECT_ID. The join to DBA_OBJECTS resolves that number into the schema-qualified name. The fully qualified form — HR.EMPLOYEES rather than just EMPLOYEES — is essential in multi-schema databases where the same table name exists under more than one owner.

object_type

The kind of database object that is locked: TABLE, INDEX, SEQUENCE, PROCEDURE, and so on. Most DML contention in OLTP systems occurs on TABLE objects. An INDEX appearing here often indicates a function-based index or a domain index is involved. A SEQUENCE lock at mode 3 is normal and extremely brief.

The DECODE on l.block — blocking status

V$LOCK.BLOCK uses three values to describe how a lock interacts with other sessions:

ValueLabelMeaning
0Not BlockingLock is held but no other session is currently waiting for it
1BlockingAt least one other session is waiting for a lock this session holds
2GlobalLock involves another RAC node; cross-instance blocking in a Real Application Clusters environment

During an incident, focus first on rows with Blocking status. Rows with Not Blocking are held but harmless in the immediate term. Global rows require checking the other RAC node using GV$LOCKED_OBJECT and GV$LOCK.

The DECODE on v.locked_mode — lock strength

Oracle assigns integer codes to the six lock modes that DML and DDL operations can acquire. The DECODE translates these codes into readable labels:

CodeLabelDescription
0NoneNo lock held in this record
1NullClaimed but not restrictive; used in distributed environments
2Row-S (SS)Sub-share; prevents full-table exclusive locks only
3Row-X (SX)Sub-exclusive; the mode acquired by standard INSERT, UPDATE, DELETE
4SharePrevents DML from other sessions on the whole table
5S/Row-X (SSX)Share sub-exclusive; prevents most DML and some DDL
6ExclusiveFull exclusive; blocks all DML and DDL from any other session

In a healthy OLTP environment, almost all locks are mode 3 (Row-X), held for milliseconds during a commit cycle. Mode 4 (Share) or mode 6 (Exclusive) points to an explicit LOCK TABLE statement, a DDL operation, or a bulk-load utility. A mode 6 lock held by an idle session with a large LAST_CALL_ET almost always means an uncommitted interactive transaction left open by a developer.

FROM and WHERE clauses

The query uses comma-style implicit join syntax across the four views. The three join conditions are:

  • v.object_id = d.object_id — joins V$LOCKED_OBJECT to DBA_OBJECTS to convert the stored numeric object ID into a readable name and type.
  • v.object_id = l.id1 — joins to V$LOCK. For table-level lock entries, ID1 in V$LOCK holds the object ID. This join retrieves the BLOCK flag and the numeric LMODE value.
  • v.session_id = s.sid — joins to V$SESSION to pull the Oracle username, OS username, and the SID,SERIAL# pair for each session holding a lock.

ORDER BY clause

Ordering first by oracle_username then by session_id groups all locks held by the same user or application account together. When a single service account holds locks across five tables, those five rows appear consecutively, making it easy to see the full scope of one open transaction without scanning the whole result set.

Tracing the Full Blocking Chain

The four-view join identifies every session holding a lock, but in a busy incident you often need to see the cascade tree: which session is the ultimate root of a chain, and how many sessions branch from it. V$SESSION.BLOCKING_SESSION stores the SID of the session that directly blocks each waiting session. Two supplementary queries use that column to build the full picture without re-joining the locked-object views.

 1-- Step 1: All sessions currently waiting on another session's lock
 2SELECT  s.sid                  AS blocked_sid
 3,       s.serial#
 4,       s.username             AS blocked_user
 5,       s.blocking_session     AS blocker_sid
 6,       s.status
 7,       s.last_call_et         AS wait_seconds
 8,       s.event                AS wait_event
 9FROM    v$session s
10WHERE   s.blocking_session IS NOT NULL
11ORDER BY s.blocking_session
12,        s.sid;
13
14-- Step 2: The root holder — blocks others but is not itself blocked
15SELECT  s.sid
16,       s.serial#
17,       s.username
18,       s.osuser
19,       s.status
20,       s.last_call_et         AS idle_seconds
21,       t.start_time           AS txn_open_since
22FROM    v$session    s
23        JOIN v$transaction t ON t.addr = s.taddr
24WHERE   s.sid IN (
25          SELECT  blocking_session
26          FROM    v$session
27          WHERE   blocking_session IS NOT NULL
28        )
29AND     s.blocking_session IS NULL
30ORDER BY s.last_call_et DESC;

Reading step 1

The first query returns one row per waiting session. BLOCKER_SID points to the session that must release its lock for the waiter to continue. When several rows share the same BLOCKER_SID, a single uncommitted transaction is cascading across multiple waiting sessions. WAIT_EVENT identifies what the waiting session is blocked on — for row-level DML conflicts the event is typically enq: TX - row lock contention; for table-level DDL conflicts it is enq: TM - contention.

Reading step 2

The second query isolates the root cause: the session holding a lock that blocks others, but not itself waiting for any other session. Two fields here drive the decision on whether to escalate:

  • IDLE_SECONDS (LAST_CALL_ET) — how many seconds since the session last made a database call. A large value combined with STATUS = INACTIVE is the signature of an open, uncommitted transaction belonging to a session that has stopped responding. This is the most common cause of multi-session blocking cascades in OLTP systems.
  • TXN_OPEN_SINCE — the wall-clock timestamp when the holding transaction started, from V$TRANSACTION.START_TIME. This establishes the age of the transaction independent of session activity. A transaction open for three hours during an overnight batch is usually an application defect, not a brief lock acquisition.

Together, the two queries answer the three questions a DBA needs before escalating: how many sessions are waiting, who is the ultimate root holder, and how long that session has been idle or its transaction has been open.

Finding downstream sessions blocked by an intermediate blocker

A chain can have multiple levels: A blocks B, B blocks C, C blocks D. Session B appears in both queries — it is blocked by A (step 1) and is also a blocker for C (step 2 would miss it because blocking_session IS NOT NULL). To expose the full chain, modify step 2 to remove the AND s.blocking_session IS NULL filter and instead include the depth level:

 1-- Full chain with immediate blocker shown for each session
 2SELECT  s.sid
 3,       s.blocking_session     AS blocked_by
 4,       s.username
 5,       s.status
 6,       s.last_call_et         AS idle_seconds
 7,       s.event
 8FROM    v$session s
 9WHERE   s.sid IN (
10          SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL
11          UNION
12          SELECT sid             FROM v$session WHERE blocking_session IS NOT NULL
13        )
14ORDER BY s.blocking_session NULLS FIRST, s.sid;

This version includes both holders and waiters in one result set. Sessions with a NULL BLOCKED_BY are root holders; sessions with a value in that column are somewhere in the wait chain. Walking the tree from root to leaf shows the complete cascade scope.

Key Points

  • V$LOCKED_OBJECT captures all active DML locks. Every object locked by an open transaction appears here regardless of mode. The row persists until the transaction commits, rolls back, or the session is terminated.
  • BLOCK = 1 is the incident indicator. A row with Not Blocking is held but currently harmless. The Blocking row is the root cause to address first.
  • SID,SERIAL# is the only safe kill handle. Using SID alone risks targeting a different session that has taken the slot since the original disconnected. Always capture both values before issuing ALTER SYSTEM KILL SESSION.
  • Locks clear automatically at commit or rollback. No manual cleanup is needed once the holding transaction ends. Re-running the query after the transaction closes will show those rows gone.
  • In RAC, use the GV$ equivalents. GV$LOCKED_OBJECT, GV$LOCK, and GV$SESSION add an INST_ID column and cover all cluster nodes. A blocker on node 2 is invisible to the node-1 V$ views.
  • In a CDB (Oracle 12c and later), container context matters. Connected to a PDB, V$LOCKED_OBJECT shows only that PDB's locks. Connected to the root (CDB$ROOT) with the CONTAINER = ALL clause, the view includes locks across all PDBs and exposes a CON_ID column that identifies which pluggable database each lock belongs to. For cross-container lock investigations on 12c and later, use CDB_OBJECTS in place of DBA_OBJECTS to resolve object names across containers.
  • BLOCKING_SESSION is a direct pointer. Available since Oracle 10g Release 1, V$SESSION.BLOCKING_SESSION holds the SID of the session blocking a waiting session, and BLOCKING_SESSION_STATUS (introduced in 11g) explains why it may be NULL (NO HOLDER, UNKNOWN, NOT IN WAIT, or VALID). These two columns are the fastest route to the root cause when the full four-view join is not convenient.
  • Privilege requirement. The query accesses DBA_OBJECTS and the fixed V$ views. The executing account needs SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY. Running as a DBA account avoids privilege errors.

Insights and Best Practices

Follow the blocking chain, not just the first row

One blocking session can cascade into a queue. Session A holds a lock; Session B is waiting for it and is therefore blocking Session C; Session C is blocking Session D. The BLOCK = 1 row identifies only the root holder — the one whose commit or rollback would free everyone downstream. To trace the full chain, query V$SESSION.BLOCKING_SESSION starting from the waiting sessions and follow the links upward, using the supplementary queries shown above. Knowing the chain length tells you how many users or jobs are affected and how urgently to respond.

Check the session before deciding to kill

Before issuing ALTER SYSTEM KILL SESSION, confirm the session is genuinely stuck rather than mid-transaction. In V$SESSION, a STATUS of INACTIVE combined with a large LAST_CALL_ET (seconds since the last database call) is the classic profile of a session that has not communicated with the database for an extended period — a developer who ran an UPDATE, confirmed the results, and walked away without committing. A session with ACTIVE status may be mid-processing and should be treated differently.

Idle open transactions are the most common source of blocking

In OLTP environments, the most frequent locking incident is not application code with a defect — it is an interactive session that executed DML and never committed. SQL*Plus, SQL Developer, and many database IDE tools default to manual-commit mode. A developer who runs UPDATE emp SET salary = salary * 1.1 WHERE empno = 7934, reviews the result, and then takes a break has locked that row for the duration. A monitoring query that alerts when any BLOCK = 1 session has LAST_CALL_ET > 300 catches this pattern proactively without requiring a DBA to watch continuously.

Distinguish row-level from table-level locks by mode

Standard INSERT, UPDATE, and DELETE acquire mode 3 (Row-X). This holds only the specific rows modified, not the entire table — other sessions can modify different rows simultaneously. Mode 4 (Share) or mode 6 (Exclusive) signals a full-table lock from an explicit LOCK TABLE statement, a DDL operation, or a legacy import utility. A table-level lock from an unexpected session is more urgent because it blocks all DML against that object, not just conflicting row-level operations.

Use this query as step one in a locking runbook

A structured response to lock contention saves time and avoids missteps. Step one: run this query to identify the blocking session's SID,SERIAL#, the object locked, and the lock mode. Step two: query V$SESSION.SQL_ID for that SID to find the last SQL the session executed. Step three: look up the SQL text in V$SQL using that SQL_ID. Those three steps — who is blocking, what session, what statement — produce the information the application team needs to investigate and give the DBA the evidence needed to justify escalation to a forced kill if the application cannot recover.

Create a scheduled lock-monitoring check

For databases where lock contention is a recurring operational concern, wrap this query in a DBMS_SCHEDULER job or a monitoring-tool alert rule that fires when BLOCK = 1 rows appear alongside LAST_CALL_ET exceeding a threshold — for example, 300 seconds. Proactive alerting removes the lag between "lock appears" and "DBA is notified," which in overnight batch windows can be the difference between resolving the issue in minutes and discovering it when the batch has missed its 06:00 completion deadline.

When to Use This Check

  • When an application or user reports that a transaction is hanging with no error returned.
  • Before a maintenance window that includes DDL, to confirm no active DML transactions are in flight on the target objects.
  • During high-concurrency periods such as month-end processing or batch settlement windows, as part of a routine operational check.
  • When AWR or Active Session History shows a rise in enq: TX - row lock contention or enq: TM - contention wait events.
  • After a new application deployment, to verify the new code does not hold transactions open longer than the previous version.
  • When a bulk data load or import appears to stall without returning an error to the calling process.
  • During on-call troubleshooting when the cause of a slowdown is unknown and lock contention is one of the candidates to rule in or out.
  • When the V$SESSION.BLOCKING_SESSION chain query reveals a multi-level cascade and you need the object names and lock modes to construct a full incident report.

Troubleshooting Common Issues

If the query returns no rows, no DML locks are currently held. The contention may have resolved itself between the report and the query, or the issue is not a lock at all — CPU saturation, full undo tablespace, or a network hang at the application tier can all produce application-side hangs without any lock visible in these views. Re-run the query at the next reported incident rather than concluding that locking is not a factor.

If the query returns many Not Blocking rows for the same session with mode Row-X, that session is holding locks across many rows or objects but is not stopping anyone else. This is normal for a large batch transaction that has not yet committed. It becomes a concern only if the session is inactive and the transaction is old. Check V$TRANSACTION.START_TIME to measure how long the transaction has been open.

If the step-2 blocking-chain query (V$SESSION.BLOCKING_SESSION) returns NULL for sessions you expect to be blockers, check V$SESSION.BLOCKING_SESSION_STATUS. A value of UNKNOWN can appear in RAC environments when the blocking session is on a different node — switch to GV$SESSION and filter by INST_ID to find the holder across all instances. A value of NO HOLDER means the session is waiting on a lock that has no current holder — this can occur briefly during a deadlock resolution sequence.

If you see Block = Global rows in a single-instance environment where RAC is not expected, verify that the CLUSTER_DATABASE initialization parameter is set to FALSE. This status can appear as an artifact after migrating from a RAC cluster to a standalone instance without resetting the parameter.

If the query fails with ORA-00942: table or view does not exist, the executing account lacks the privilege to see DBA_OBJECTS or the V$ fixed-view synonyms. Grant SELECT_CATALOG_ROLE, or run the query as a user with DBA privileges.

References

Posts in this series