Identify Locked Objects in Oracle Database

calendar Sep 3, 2024 · 3 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

The provided Oracle Database code is a SQL query designed to identify locked objects within a database. It provides detailed information about the locked objects, including the username, session ID, object name, object type, blocking status, and the mode in which the object is held using tables v$locked_object & v$session

Sample SQL Command

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

Code Breakdown

  1. Setting the Display Parameters:
    • set lines 100 pages 999: These commands adjust the display format of the query results, increasing the number of lines per page and the overall number of pages.
  2. Column Formatting:
    • col username format a20, col sess_id format a10, col object format a25, col mode_held format a10: These commands specify the format for each column in the query results, ensuring consistent alignment and readability.
  3. Query Body:
    • SELECT Clause: Specifies the columns to be retrieved.
      • oracle_username || ' (' || s.osuser || ')' concatenates the Oracle username with the operating system user for better identification.
      • s.sid || ',' || s.serial# combines the session ID and serial number for unique identification.
      • owner || '.' || object_name constructs the fully qualified object name.
      • object_type retrieves the object type (e.g., TABLE, INDEX).
      • decode statements are used to convert numeric codes for blocking status and mode_held into human-readable descriptions.
    • FROM Clause: Joins multiple views to retrieve the necessary information.
      • v$locked_object: Contains information about locked objects.
      • dba_objects: Provides metadata about database objects.
      • v$lock: Contains information about locks.
      • v$session: Contains information about database sessions.
    • WHERE Clause: Filters the results based on the relationships between the different views.
    • ORDER BY Clause: Sorts the results by Oracle username and session ID for easier analysis.

Key Points and Insights

  • The query provides a comprehensive overview of locked objects, including their owners, session information, object types, blocking status, and lock modes.
  • The decode statements enhance readability by converting numeric codes into descriptive labels.
  • The ORDER BY clause helps organize the results for easier analysis.
  • Understanding the structure of the v$locked_object, dba_objects, v$lock, and v$session views is essential for interpreting the query results.
  • This query can be used to identify and troubleshoot performance issues caused by locked objects.

References

Posts in this series