Re: A row locking problem that baffles all...

On Thu, 14 Jan 1999 09:35:58 +0800, Connor McDonald <mcdonald.connor.cs_at_bhp.com.au> wrote:

>We use the following view to assess locking on the database...It shows
>username, sid and object_name and the "block" column can be used to
>determine if there is blocking going on...
>
>It should be quite easily tweaked to provide what you need..
>
>Cheers
>
>create view lock_contention is
>SELECT substr(S.USERNAME,1,8),
> S.SID,
> L.TYPE,
> L.ID1,
> substr(o.name,1,22),
> DECODE(L.LMODE, 0, 'NONE', 1, 'NULL', 2, 'SS', 3, 'RX',
> 4, 'S', 5, 'SRX', 6, 'X ', '?'),
> DECODE(L.REQUEST, 0, 'NONE', 1, 'NULL', 2, 'SS', 3, 'RX',
> 4, 'S', 5, 'SRX', 6, 'X', '?'),
> l.ctime,
> decode(l.block,0,null,'TRUE')
>FROM V$LOCK L, V$SESSION S, obj$ o
>WHERE L.SID=S.SID
>AND nls_upper(S.USERNAME) LIKE nls_upper(null)|| '%'
>and l.id1 = o.obj# (+)
It does work if there is no NOWAIT on the row locking query. Unfortunately my situation uses NOWAIT.

Paul
aspscott_at_tcp.co.uk
^^ remove 'as' anti spam prefix to reply Received on Thu Jan 14 1999 - 21:06:42 CET