Re: Who locks who ? script needed

From: Vincent Ventrone <moe_at_nospam.com>
Date: Thu, 7 Apr 2005 16:46:35 -0400
Message-ID: <d3467a$rb6$1@courier.brandeis.edu>

> It gives you the OS Server process ID as well, and it is now easy to call
> the user who did not commit it transaction to release the lock and let the
> other transaction proceed. You can kill the server process as well.
>
> I guess a FREE product or script that does the same exists ?
>
> I have seen various lock monitoring scripts, but none of them is easy to
> use
> or does exactly the same thing. We do not use OEM diagnostics pack either.
>

fwiw I use this query:

column resrce format a25 heading 'RESOURCE (Type-ID1-ID2)' column usr_sid format a10 heading 'User/SID' break on resrce
set echo off
set feedback on
spool locks_held_wanted.lst

select /*+ ordered */
l.type || '-' || l.id1 || '-' || l.id2 as resrce, nvl(b.name, lpad(to_char(l.sid), 4)) as usr_sid, decode(
l.lmode,

1, ' N',
2, ' SS',
3, ' SX',
4, ' S',
5, ' SSX',
6, ' X'

) holding,
decode(
l.request,

1, ' N',
2, ' SS',
3, ' SX',
4, ' S',
5, ' SSX',
6, ' X'

) wanting,
l.ctime seconds
from

sys.v_$lock l,
sys.v_$session s,
sys.v_$bgprocess b

where
s.sid = l.sid and
b.paddr (+) = s.paddr
order by
l.type || '-' || l.id1 || '-' || l.id2,
sign(l.request),
l.ctime desc
/
spool off Received on Thu Apr 07 2005 - 15:46:35 CDT