RE: determine which row in a table is currently locked?

From: Steve Adams <steve.adams_at_ixora.com.au>
Date: Sun, 24 Oct 1999 21:45:40 +1000
Message-ID: <01BF1E69.ABAD3D80.steve.adams@ixora.com.au>

Hello David,

I'm afraid that this is not feasible.
Row-level locks only exist in the data blocks themselves. And whether they are still in force is non-trivial because of delayed block cleanout.

Regards,
Steve Adams

http://www.ixora.com.au/

http://www.oreilly.com/catalog/orinternals/

http://www.christianity.com.au/



-----Original Message-----
From:	David Spaisman [SMTP:david.spaisman_at_compaq.com]
Sent:	Friday, October 22, 1999 5:39 AM
To:	comp.databases.oracle.server_at_list.deja.com
Subject:	determine which row in a table is currently locked?

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
/

Deja.com: Before you buy.
http://www.deja.com/

To modify or remove your subscription, go to http://www.deja.com/edit_sub.xp?group=comp.databases.oracle.server
Read this thread at http://www.deja.com/thread/%3C7unqan%241ns%241%40mailint03.im.hou.compaq.com%3E

Sent via Deja.com http://www.deja.com/ Before you buy. Received on Sun Oct 24 1999 - 06:45:40 CDT