You shouldn't need to do that. To find out who the holders are of all
latches currently being aited on by ithers you could do
SELECT lh.sid, ln.name, sq.sql_text
FROM v$latchholder lh, v$sqlarea sq, v$session se, v$session_wait sw,
v$latchname ln
WHERE sw.event = 'latch free'
AND sw.p1raw = lh.laddr
AND ln.latch# = sw.p2
AND se.sid = lh.sid
AND se.sql_address = sq.address
AND se.sql_hash_value = sq.hash_value
// George Schlossnagle
// Principal Consultant
// OmniTI, Inc http://www.omniti.com
// (c) 301.343.6422 (e) george_at_omniti.com
// 1024D/1100A5A0 1370 F70A 9365 96C9 2F5E 56C2 B2B9 262F 1100 A5A0
On Tuesday, February 19, 2002, at 11:28 AM, Diego Cutrone wrote:
> you'll have to take a library cache dump and look for the latch number
> displayed in the P1 Field of your query.
>
> HTH
> Greetings
> Diego Cutrone
>
> ----- Original Message -----
> From: Gupta, Brijesh
> To: Multiple recipients of list ORACLE-L
> Sent: Friday, February 15, 2002 12:08 PM
> Subject: Library Cache wait -- Who is holding this latch
>
> Hi All
> I have a session which is waiting for Library Cache latch for 6
> hours. How can I find who is holding this latch.
> This session is executing a pl/sql script ( Not package ). Latch# 60
> is library cache latch.
>
>
> Here is from v$session_wait
>
> 1 select * from v$session_wait
> 2* where sid=1005
> PROD>/
> Press Enter to Continue ....
>
> SID SEQ# EVENT
> P1TEXT
> ------- ----------- ------------------------------
> -------------------------------------------------
> P1RAW
> P2TEXT
> P2 P2RAW
> ----------------
> ----------------------------------------------------------------
> ----------- ------
>
> Wait Sec in
> P3TEXT
> P3 P3RAW time Wait STATE
> ----------------------------------------------------------------
> ----------- ---------------- ------
> 1005 5987 latch free
> address @9925
> 07000001C0A5E228
> number
> 60 000000000000003C
> tries
> 0 00 -1 32,962 WAITED SHORT
>
>
>
>
> Thanks
>
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Brijesh Gupta
> Oracle Production DBA
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: George Schlossnagle
INET: george_at_omniti.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Tue Feb 19 2002 - 13:29:15 CST
