Re: locks

In article <3A2584D6.20D41B22_at_wxs.nl>,
  Raymond van Daelen <daelen_at_wxs.nl> wrote:
>
>
> wegorz wrote:
> >
> > Try this one:
> >
>
> Thanks,
> almost the answer, now I can see the table, but still not what records
> are actually locked. I also found out that it is possible to select
 the
> SQL statement that the user who holds the lock executes, whixh should
> lead to the rows locked (and, even better, what causes the locK) by
> issuing:
>
> select s.sid
> ,s.username
> ,s.osuser
> ,NVL(s.machine,'?') machine
> ,NVL(s.program,'?') program
> ,s.process F_Grund
> ,p.spid B_Ground
> ,x.sql_text
> from sys.v_$session s
> ,sys.v_$process p
> ,sys.v_$sqlarea x
> where s.osuser like lower(NVL('&OS_User','%'))
> and s.username like upper(NVL('&Oracle_user','%'))
> and s.sid like NVL('&SID','%')
> and s.paddr = p.addr
> and s.type != 'BACKGROUND'
> and s.sql_address = x.address
> and s.sql_hash_value = x.hash_value
> order by s.sid
>
> however, for some reason i do not get the sqlstatement, even thoudg
> there really is a lock and the sql statement is present (in sqlarea I
> can find it!) ?
>
> Raymond
>

Oracle does not maintain a record of the row level locks anywhere in the shared pool; this information is not in the v$ views. Oracle maintains this information in the actual Oracle data blocks using the transaction work areas so the only way to find locked rows is to query them!

If a session is waiting on a row level lock you can see the rowid of the row being waited on in the v$session entry for the waiting session, but you can not see all locked rows in the target block or object.

--
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.