When a record is locked ,can we know which one is locked?

How can I get the record rowid or something else info?

I can get some info by this sql

   FROM v$locked_object a, dba_objects b, v$session c    
WHERE a.object_id = b.object_id    
    AND a.SESSION_ID = c.sid(+) 

I found a method in web to get rowid by using function DBMS_ROWID.ROWID_CREATE()

But it doesn't seem to work.

  • 2
    You can only see locks that some process is waiting for, not those that are held by a transaction. Mar 1, 2013 at 14:09
  • @a_horse_with_no_name - v$lock shows you the locks held by a transaction Mar 1, 2013 at 16:03
  • @ChrisSaxon: You are right. I was referring to the fact that you cannot see which rows have been locked - I should have been more clear. Mar 1, 2013 at 16:13
  • Oracle (on contrary from other databases) does not have any shared structure for locks. This makes the database scalable, but in the other hand you can not see all locks. Locks are stored in database blocks directly. In the moment when somebody gets blocked a structure "holder-waiter" is created. Then you will see this pair in V$LOCK.
    – ibre5041
    Apr 3, 2015 at 8:38

2 Answers 2


You can't really list all rows that are being locked by a session. However, once a session is being blocked by another, you can find which session/row is blocking it.

Oracle doesn't maintain a list of individual row locks. Rather, locks are registered directly inside the rows themselves -- think of it as an extra column.

You can find which session has acquired a lock on an object through the V$LOCK view, but this will only list general information, not at the row level.

With this view you can also find if a session is being blocked by another. In that case, if a session is blocked by another session, the row information is displayed in the V$SESSION information.

You can retrieve the rowid, let's build an example with 2 sessions:

SESSION1> create table test as select * from all_objects;

Table created

SESSION1> select rowid from test where object_name = 'TEST' for update;


/* setting identifiers to help with identifying this session later */
SESSION2> exec dbms_application_info.set_client_info('012345');

PL/SQL procedure successfully completed

SESSION2> select 1 from test where object_name = 'TEST' for update;
/*  this will block */

Session 2 is now waiting on Session 1. We can discover the blocking row with:

SESSION1> SELECT o.object_name,
       2         dbms_rowid.ROWID_CREATE (1,
       3                                  s.ROW_WAIT_OBJ#,
       4                                  s.ROW_WAIT_FILE#,
       5                                  s.ROW_WAIT_BLOCK#,
       6                                  s.ROW_WAIT_ROW#) rid
       7     FROM dba_objects o, v$session s
       8    WHERE o.object_id = s.row_wait_obj#
       9      AND s.client_info = '012345';

--------------- ------------------

For further reading: a description of the process by Tom Kyte.


You can find all tables locks in an Oracle database by running the following query

   v$locked_object a ,
   v$session b,
   dba_objects c
   b.sid = a.session_id
   a.object_id = c.object_id;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.