Re: Query to show db locks?

Don't forget to look at latches as well as locks. Better to look into v$session_wait.

On 6 Dec 2004 12:59:32 -0800, "Stefan" <stefan_at_dragolov.com> wrote:

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

.......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes remove NSPAM to email Received on Tue Dec 07 2004 - 10:26:04 CST