Re: How to know which row is locked ?

On Sat, 3 Oct 1998,Clive Bostock wrote:

> I'm afraid it's not possible. The locking is done inside the database
> block header. This is what make Oracle so scalable. Unfortunatley
> there is no way (supplied by Oracle) to determine what row is locked.
  

Nothing is impossible! >:-)

It seems to me that the only time that a lock is a problem is if someone else gets caught up waiting for it. When that happens you can find out the sid of who is waiting by looking at v$session_wait, for a session waiting on an 'enqueue' event.   

Then you can go look at v$session for that sid, and you will see columns in there for ROW_WAIT_FILE#, ROW_WAIT_BLOCK# and ROW_WAIT_ROW#. Then you can use the sprintf function to return the rowid thusly:

$rowid = sprintf("%08X.%04X.%04X", $block, $row, $file);

So, for instance, if you have found a session waiting for the lock:

13:35:40 SQL> r
  1 select ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#   2 from v$session
  3* where sid = 33

ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# ------------- -------------- --------------- -------------

         1679 288 1689 16

You could use perl or anything else that can use the standard sprintf function to return the rowid:

unixhost% perl -e 'print sprintf("\n%08X.%04X.%04X\n\n",1689,16,288);'

00000699.0010.0120

unixhost%

If you don't have something this nifty, you can use the windows calculator in "scientific" mode to turn the decimal numbers from v$session into hex to form the rowid in the form "block.row.file".

--
Jeremiah Wilton      http://www.wolfenet.com/~jeremiah