Lock

[pcotten]

Is there a way for a user to see who is holding lock on a row? For example, if User #1 is updating a row in a table and holds lock on that, can another user while trying to access the same row find out who is holding the lock? Is there a mechanism to do this? I thought since users don't have access to v$lock etc., they can't get that info. Thanks.

[hbk_emp]

yes there is what version of database are you using?

[pcotten]

We are on 8.0.6

[amar]

You can give your users select permission on v_$lock (and not on v$lock - this applies to all v$ views) so that they can see the data. It is NOT ADVISABLE to directly give this right though!

What you should do is to create a procedure/function in the SYS/SYSTEM schema (any DBA schema will do) to return the relevant information to the user. You have to give execute permission on the procedure/function to the user(s) but no select on any v_$'s necessary. This will be more secure as you have control over the info you pass to the user(s).

This will work regardless of your oracle version (should work!).

-amar

[jmodic]

As I've written a couple of times in this forum: *there is no way* to find out:
a) who is locking a particular row
b) which rows are locked by a particular user

The reason for this is that row locks are held in a database blocks themselves, no system view/table holds this information. What you can find out from the data dictionary is only:
a) who is having a lock on a particular table
b) what kind of lock he/she holds on that table

Example:

5 diferent users have some different rows locked on the same table TABLE_A. Now you want to change one of those locked rows. Of course your update will fail and now you want to find out who is locking this row. All that you'll be able to find out will be which 5 users have locks on that table TABLE_A, but you won't be able to find out which of those 5 users is holding lock on your row. Of course, in case only one user has got lock on that table when your update failed you know that that user was holding a lock on your row, but this is a very particular case, not a general answer for your general problem.

[Zaggy]

There is a document on Metalink on how to get rowids for users on a specific object. The document number is 132629.1. The document requires you to use Pro*C, but explains everything.