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

In article <7unqan$1ns$1_at_mailint03.im.hou.compaq.com>,   "David Spaisman" <david.spaisman_at_compaq.com> wrote:
> 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
> /
>

Oracle does not store row level locking information in the SGA; it is maintained in the database data blocks so there is no way to see all locked rows. You must access the data to see if it is locked. However, v$session does contain (since 7.2) columns for the file number, object number, block number, and row number that a session is waiting on. This may be of some help to you. Note that v$lock is a list of resource and object level locks. Even though Oracle says they only lock at the row level, a pending update of a row must block certain other object level activity from taking place until the transaction completes so all row level locks basically result in an associated table level lock which appears in v$lock.

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Oct 22 1999 - 09:31:26 CDT