How to Find Locked Objects in Oracle Apps R12

Essential SQL queries for DBAs and Developers to diagnose and resolve locking issues.

Understanding Database Locks

In a multi-user database environment like Oracle, locks are a crucial mechanism to prevent data corruption by ensuring that only one session can modify a particular piece of data at a time. However, sometimes sessions can hold locks for too long, causing other users to wait and leading to performance bottlenecks. These queries will help you identify which objects are locked and who is holding the lock.

Query 1: Basic Lock Information

This is the simplest query to quickly find out which objects are locked and which database and OS user is holding the lock.

SQL Query

SELECT b.owner
     , b.object_name
     , a.oracle_username
     , a.os_user_name
FROM   v$locked_object a
     , all_objects b
WHERE  a.object_id = b.object_id

Query 2: Detailed Session Information

This query joins with v$session to provide more context about the session holding the lock, such as the Session ID (SID), serial number, status, and the machine the user is connected from.

SQL Query

SELECT c.owner
     , c.object_name
     , c.object_type
     , b.SID
     , b.serial#
     , b.status
     , b.osuser
     , b.machine
FROM   v$locked_object a
     , v$session b
     , dba_objects c
WHERE  b.SID = a.session_id
AND    a.object_id = c.object_id

Query 3: Comprehensive Lock Details

This query provides a comprehensive view, including the session ID, user details, object information, and the type of lock being held (e.g., exclusive, shared).

SQL Query

SELECT a.session_id
     , a.oracle_username
     , a.os_user_name
     , b.owner "OBJECT OWNER"
     , b.object_name
     , b.object_type
     , a.locked_mode
FROM   ( SELECT object_id
              , session_id
              , oracle_username
              , os_user_name
              , locked_mode
        FROM   v$locked_object ) a
     , ( SELECT object_id
              , owner
              , object_name
              , object_type
        FROM   dba_objects ) b
WHERE  a.object_id = b.object_id

Pro Tip: Resolving a Lock

Once you have identified the blocking session using the queries above, you may need to terminate it. Use the `ALTER SYSTEM KILL SESSION` command with caution, as it will immediately stop the user's transaction. Always confirm with the user or application owner before killing a session in a production environment.

SQL Query

-- First, find the SID and SERIAL# from Query 2 or 3
-- Then, use the following command to kill the session
-- Be very careful with this command in a production environment

ALTER SYSTEM KILL SESSION 'sid,serial#';

-- Example:
ALTER SYSTEM KILL SESSION '123,4567';