Re: Query to show db locks?

"Stefan" <stefan_at_dragolov.com> wrote in message news:1102366772.541261.141640_at_c13g2000cwb.googlegroups.com...
> I'm looking for a query that will give me locks information including
> schema and object information. This information is available in Oracle
> Enterprise Manager (OEM) and is displayed relatively quickly...
> I tried using the query below but it takes way too long (I'm not sure
> why, it doesn't seem like it should).
>
> Does anyone have a better query or know of the query that is used in
> OEM to generate this information?
>
> Thanks
>
>
> select b.sid,
> c.username,
> c.osuser,
> c.terminal,
> decode(b.id2, 0, a.object_name, 'Trans-'||to_char(b.id1)) object_name,
> b.type,
> decode(b.lmode,
> 0, '--Waiting--',
> 1, 'Null',
> 2, 'Row Share',
> 3, 'Row Excl',
> 4, 'Share',
> 5, 'Sha Row Exc',
> 6, 'Exclusive',
> 'Other') "Lock Mode",
> decode(b.request,
> 0, ' ',
> 1, 'Null',
> 2, 'Row Share',
> 3, 'Row Excl',
> 4, 'Share',
> 5, 'Sha Row Exc',
> 6, 'Exclusive',
> 'Other') "Req Mode"
> from dba_objects a,
> v$lock b,
> v$session c
> where a.object_id (+) = b.id1
> and b.sid = c.sid
> and c.username is not null
> and c.sid = &session_id
> order by
> b.sid,
> b.id2

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,

         id1, id2, lmode, request, type
    FROM V$LOCK
   WHERE (id1, id2, type) IN

             (SELECT id1, id2, type FROM V$LOCK WHERE request>0)    ORDER BY id1, request
/> Received on Mon Dec 06 2004 - 17:55:39 CST