How to find the locked object in Oracle

We can find the locked object’s identity by looking at the value of the ID1 (LockIdentifier) column in the V$LOCK view. The value of the ID1 column where the TYPE column is TM (DML enqueue) identifies the locked object. Let's say we've ascertained that the value of the ID1 column is 99999. We can then issue the following query to identify the locked table:

SQL> select object_name from dba_objects where object_id=99999;

OBJECT_NAME
----------------------
TEST
SQL>

An even easier way is to use the V$LOCKED_OBJECT view to find out the locked object, the object type, and the owner of the object.

SQL> select lpad(' ',decode(l.xidusn,0,3,0)) || l.oracle_username "User",
o.owner, o.object_name, o.object_type
from v$locked_object l, dba_objects o
where l.object_id = o.object_id
order by o.object_id, 1 desc;

User OWNER      OBJECT_NAME OBJECT_TYPE
-------- -----------       ---------------------- ----------------------
HR            HR TEST           TABLE
SH            HR TEST           TABLE


Note that the query shows both the blocking and the blocked users.

We can also use Oracle Enterprise Manager to quickly identify a locked object, the ROWID of the object involved in the lock, and the SQL statement that’s responsible for the locks. However, it’s always important to understand the underlying Oracle views that contain the locking information, and that’s what this post demonstrates. Using the queries shown above, we can easily identify a locked object without recourse to a monitoring tool such as Oracle Enterprise Manager, for example.