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) [EMAIL PROTECTED]
// 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
