Locked Objects Analysis (vlockobj.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script provides a comprehensive view of locked database objects and identifies blocking relationships. It shows which sessions are holding locks on objects and which sessions are blocked waiting for those locks, along with detailed lock mode information to help resolve lock contention issues.

Script

rem  vlockobj.sql
rem
ttitle 'Locked Objects'
rem
set lines 132
rem
col oracle_username format a10 heading 'ORACLE|USERID'
col os_user_name format a9 heading 'OS|USERID'
col session_id format 99999 heading 'SID'
col process format a7 heading 'OS PID'
rem col locked_mode format 9 heading 'M'
col locked_mode format a13 heading 'LOCKED MODE'
col object_id format 99999 heading 'OBJECT|ID'
col owner format a8 heading 'OWNER'
col object_type format a7 heading 'OBJECT|TYPE'
col object_name format a20 heading 'OBJECT NAME'
col blocker format a13 heading 'BLOCKER'
col lmode format 999 heading 'LMDE'
col request format 999 heading 'REQ'
col module format a10 heading 'MODULE'
col id1 format 99999999 heading 'ID1'
col id2 format 99999999 heading 'ID2'
rem
rem 0, None
rem 1, Null (NULL)
rem 2, Row-S (SS)
rem 3, Row-X (SX)
rem 4, Share (S)
rem 5, S/Row-X (SSX)
rem 6, Exclusive (X)
break on owner on object_type on object_name on id1 on id2 skip 1
rem
select /* NOTE: had to use subqueries for dba_objects
    due to poor performance in underlying hints
    sys.X$ tables */
       (select o.owner
    from dba_objects o
    where o.object_id=lo.object_id) owner,
       (select o.object_name
    from dba_objects o
    where o.object_id=lo.object_id) object_name,
       (select o.object_type
    from dba_objects o
    where o.object_id=lo.object_id) object_type,
       l.id1,
       l.id2,
       decode(l.block, 0, null, '** blocker **') blocker,
       -- lo.locked_mode,
       decode( lo.locked_mode,
          0, 'None',
          1, 'Null (NULL)',
          2, 'Row-S (SS)',
          3, 'Row-X (SX)',
          4, 'Share (S)',
          5, 'S/Row-X (SSX)',
          6, 'Exclusive (X)',
          '???') locked_mode,
       lo.session_id,
       -- l.lmode,
       -- l.request,
       lo.oracle_username,
       lo.os_user_name,
       s.module
 from  v$lock l,
       v$locked_object lo,
       v$session s
 where lo.session_id=l.sid
   and s.sid=lo.session_id
   and (l.id1, l.id2) in
      (select id1, id2
   from v$lock
  where request > 0)
 order by
    owner, object_name, object_type,
    l.id1, l.id2, l.block desc
/

Usage

-- Run the script in SQL*Plus or SQLcl
@vlockobj.sql

-- No parameters required
-- Shows current lock contention at time of execution

Required Privileges

• SELECT on V$LOCK
• SELECT on V$LOCKED_OBJECT
• SELECT on V$SESSION
• SELECT on DBA_OBJECTS
• Typically requires DBA role

Sample Output

Locked Objects

OWNER    OBJECT NAME          OBJECT      ID1      ID2 BLOCKER       LOCKED MODE     SID ORACLE     OS       MODULE
                              TYPE                                                        USERID     USERID
-------- -------------------- ------- -------- -------- ------------- ------------- ----- ---------- -------- ----------
SALES    CUSTOMER_ORDERS      TABLE      65537    12345                Row-X (SX)      145 APP_USER   jdoe     JDBC
                                         65537    12345 ** blocker **  Row-X (SX)      234 APP_USER   msmith   JDBC

HR       EMPLOYEES            TABLE      65538    67890                Share (S)       156 HR_ADMIN   admin    SQL*Plus
                                         65538    67890 ** blocker **  Exclusive (X)   278 DBA_USER   dbadmin  SQL*Plus

Key Output Columns

• OWNER: Schema owner of the locked object
• OBJECT NAME: Name of the locked object
• OBJECT TYPE: Type of object (TABLE, INDEX, etc.)
• ID1/ID2: Lock identifiers for grouping related locks
• BLOCKER: Indicates if session is blocking others
• LOCKED MODE: Type of lock held or requested
• SID: Oracle session ID
• ORACLE USERID: Database username
• OS USERID: Operating system username
• MODULE: Application or tool name

Understanding Lock Modes

Lock Mode Hierarchy

• None (0): No lock
• Null (1): Placeholder, allows all other locks
• Row-S (2): Row Share - allows concurrent reads
• Row-X (3): Row Exclusive - allows concurrent reads, prevents other writes
• Share (4): Share - allows concurrent reads, prevents all writes
• S/Row-X (5): Share Row Exclusive - allows concurrent reads of different rows
• Exclusive (6): Exclusive - prevents all other access

Lock Compatibility

• Row-S: Compatible with Row-S, Row-X, Share
• Row-X: Compatible with Row-S, Row-X only
• Share: Compatible with Row-S, Share only
• Exclusive: Not compatible with any other lock

Performance Analysis

Identifying Blocking Patterns

1. Simple Blocking: One session blocks another
2. Chain Blocking: Session A blocks B, B blocks C
3. Deadlock Potential: Circular waiting patterns
4. Mass Blocking: One session blocks many others

Common Contention Scenarios

• Table Locks: DDL operations blocking DML
• Row Locks: Multiple sessions updating same rows
• Index Locks: Unique constraint violations or index maintenance
• Application Locks: Custom locking mechanisms

Common Use Cases

Immediate Troubleshooting

-- During application hang or slowness
@vlockobj.sql
-- Identify blocking sessions for potential kill

Lock Analysis

-- Understand application lock patterns
@vlockobj.sql
-- Review lock modes and durations

Deadlock Investigation

-- After deadlock detection
@vlockobj.sql
-- Analyze remaining locks and patterns

Troubleshooting Steps

Immediate Resolution

1. Identify Blocker Sessions:

   • Look for ”** blocker **” entries
   • Note the blocking session SID

2. Contact Application Users:

   • Check with user/application owning blocking session
   • Determine if operation can be completed quickly

3. Kill Blocking Session (if necessary):

   ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Investigation Queries

-- Get more details about blocking session
SELECT sid, serial#, username, status,
       last_call_et, sql_id, blocking_session
FROM v$session
WHERE sid = &blocking_sid;

-- Check what SQL the blocker is running
SELECT sql_text
FROM v$sql
WHERE sql_id = '&sql_id_from_above';

-- Monitor lock waits in real-time
SELECT blocking_session, sid, wait_class, seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL;

Prevention Strategies

1. Application Design:

   • Use appropriate transaction scope
   • Minimize lock hold times
   • Implement timeout mechanisms

2. Database Configuration:

   • Set appropriate lock timeout values
   • Monitor for lock escalation
   • Consider lock-free alternatives

3. Coding Best Practices:

   • Use bind variables to reduce parsing locks
   • Commit transactions promptly
   • Avoid unnecessary locking

Advanced Analysis

Lock Wait Analysis

-- Current lock waiters
SELECT sw.sid, sw.event, sw.seconds_in_wait,
       s.username, s.program
FROM v$session_wait sw, v$session s
WHERE sw.sid = s.sid
AND sw.event LIKE '%enq%'
ORDER BY seconds_in_wait DESC;

Historical Lock Analysis

-- Check AWR for lock patterns
SELECT snap_id, event_name, total_waits, time_waited_micro
FROM dba_hist_system_event
WHERE event_name LIKE '%enq%'
AND snap_id > &start_snap
ORDER BY snap_id, time_waited_micro DESC;

Related Scripts

• Active Session Analysis - Current session activity and waits
• Blocking Locks Summary - Simple blocking session identification
• Lock Statistics - Overall locking statistics
• Library Cache Locks - Library cache lock analysis