determine which row in a table is currently locked?

From: David Spaisman <david.spaisman_at_compaq.com>
Date: Thu, 21 Oct 1999 15:39:19 -0400
Message-ID: <7unqan$1ns$1@mailint03.im.hou.compaq.com>

Hello:

For an application running on Oracle 8.04. on NT 4.0, I have been researching how to determine which objects are locked and by whom as well as which username, session id and object.

The sql code below was obtained from a dba support site.

What I would like to do is take this one step further. I would like to be able to determine which row in the given table is locked.

The application running is a java application and users, the way the purchased application is structured, require multiple logins to perform separate transactions. This results in users locking each other out at times. Does any one have a script which permit me to do see exactly which row in a table is locked?

If not, are there any alternatives to determine which row is locked?

Any information will be greatly appreciated. Thanks.

David Spaisman

select username un, osuser ou, s.sid,

       decode(l.type,'TM','Table','TX','Row') ty,
       decode(lmode,1,'NONE',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') mh,
       o.name ob,
       decode(request,1,'NONE',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') mr

from v$lock l, v$session s, sys.obj$ o, v$transaction t

where l.sid = s.sid
and   t.ses_addr = s.saddr
and   l.id1 = o.obj#(+)

and username is not null
order by o.name desc, osuser
/ Received on Thu Oct 21 1999 - 14:39:19 CDT