How To Indentify The Row Which is Locked By an Other User's Session

Subject: How To Indentify The Row Which is Locked By an Other User's Session
Doc ID: Note:198150.1 Type: TROUBLESHOOTING
Last Revision Date: 18-MAY-2004 Status: PUBLISHED



goal: Identify the row which is locked by an other user's session
fact: Oracle Server - Enterprise Edition



fix:

To determine which row is locked in a certain table can only be queried when an
other user is waiting for the row involved.

To find the row, 3 queries must be executed:

1) This query will display some information about the object and user involved.
The OBJECT_ID value returned must be used in the second query. The
OBJECT_NAME is used in the last query. The queries must be executed as a DBA
user.

sql> select a.os_user_name,
2 a.oracle_username,
3 a.object_id,
4 c.object_name,
5 c.object_type
6 from v$locked_object a, dba_objects c
7 where a.object_id=c.object_id
8 /

2) This query will return the ROW_WAIT_FILE#, ROW_WAIT_BLOCK# and ROW_WAIT_ROW#
values for the OBJECT_ID involved. The ROW_WAIT_FILE#, ROW_WAIT_BLOCK# and
ROW_WAIT_ROW# will be needed in the last query. If this query returns no
rows, no user is waiting for this row.

sql> select sid,
2 row_wait_obj# objn,
3 row_wait_file# fn,
4 row_wait_block# bn,
5 row_wait_row# rn
6 from v$session
7 where row_wait_obj#=
8 /

3) The final query will return the row which is locked an the user is waiting
for. The OBJN, FN, BN and RN values from the second query and OBJECT_NAME
from the first query need to be subsituted in this query.

sql> select * from
2 where rowid = dbms_rowid.rowid_create(1, , , , )
3 /