Determine who locked what row

[monseiur]

Greetings,

Can you find out if a row is locked, who has locked this row?
I need to display a username when a row in a table is locked..

Regards

[marist89]

There may be two data dictionary views that can help you in your search. The view names are dba_blockers and dba_waiters and are created by running catblock.sql from $OH/rdbms/admin. You can then decipher the session id's from v$session to determine the username.

[monseiur]

Thanks for the reply..

Is it also possible to get the row # or ROWID which is locked..
This is the scenario..

There is a table Fred has a primary key emp_no,
I try to lock the row emp_no = 20 to update by doing 'select for update nowait' but it turned out it is locked I want to know this row is locked by which user inorder to display information to the user..

How can I do that?

[jmodic]

AFAIK there is no way to find out who is holding lock on a particular row. This information is written in the database block header only, it can not be obtained from any dictionary view or even from any system's fixed table (X$*).

[miritika]

Please try using v$locked_object table.

Hope this helps u.

[jmodic]

Originally posted by miritika

Please try using v$locked_object table.

Hope this helps u.

This only displays which session has a lock on which table, you can't get the information *which row* is locked by which session. There is no way to find out which row is locked by which session, unless you do a database block dump read it from there. But I don't think this is a very usefull way to get this info....