Identifying Locked Rows in Oracle Database using tables v$session and dba_objects

calendar Sep 5, 2024 · 2 min read · v$session dba_objects Oracle Database Administration SQL  ·
Share on: linkedin copy

Identifying Locked Rows in Oracle Database using tables v$session and dba_objects

Purpose

This Oracle query pinpoints the exact row a specific session is waiting on due to a lock. It first finds the locked object and row location, then constructs a ROWID to fetch and display the entire locked row from the table.

Sample SQL Command

 1/* Show which row is locked
 2
 3select	do.object_name
 4,	row_wait_obj#
 5,	row_wait_file#
 6,	row_wait_block#
 7,	row_wait_row#
 8,	dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, 
 9				ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)
10from	v$session s
11,	dba_objects do
12where	sid=&sid
13and 	s.ROW_WAIT_OBJ# = do.OBJECT_ID
14/
15
16/* Then select the row with that rowid...
17
18select * from <table> where rowid=<rowid>;

Code Breakdown

  1. Query 1:

    • v$session s: Joins the v$session view to get information about the session.
    • dba_objects do: Joins the dba_objects view to get information about the object (table) the row is in.
    • where sid=&sid: Filters the results to only the specified session ID.
    • s.ROW_WAIT_OBJ# = do.OBJECT_ID: Matches the object ID from v$session with the object ID from dba_objects.
    • Columns:
      • object_name: The name of the table where the locked row resides.
      • row_wait_obj#: The object ID of the table.
      • row_wait_file#: The file number of the data block containing the row.
      • row_wait_block#: The block number within the file.
      • row_wait_row#: The row number within the block.
      • dbms_rowid.rowid_create: Creates a ROWID using the row_wait columns.

  2. Query 2:

    • select * from <table> where rowid=<rowid>: Selects all columns from the specified table using the ROWID obtained from the first query.

Key Points:

  • The v$session view provides information about active sessions, including the object and row they are waiting on.
  • The dba_objects view provides information about database objects, such as tables.
  • The dbms_rowid.rowid_create function is used to construct a ROWID from its individual components.
  • The ROWID is a unique identifier for a row within a table.

Insights:

  • This code can be helpful for troubleshooting locking issues in Oracle databases.
  • By identifying the locked row, you can determine the cause of the lock and take appropriate action, such as modifying the application or adjusting database settings.

Explanation:

The first query identifies the object and row that a specific session is waiting on. The second query retrieves the data from that row using the ROWID. This allows you to examine the row's contents and understand why it might be locked.

References:

Posts in this series