RENAME and Library cache pin waits

From: "Herring Dave - dherri" <Dave.Herring@xxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Date: Thu, 18 May 2006 09:45:07 -0500
I'm about to open an SR on a situation I've got with a RENAME hanging on event 
'library cache pin', but thought I'd bounce it off all of you first, in case 
anyone's got any helpful suggestions on how to further investigate this or 
resolve it.

Here's the situation:  We have an app that performs a RENAME at a certain 
point.  There's a chance the table won't exist, so the statement should fail 
with an ORA-04043.  I have no control over the app so I can't have the code 
check for existence of the table first or use ALTER TABLE ... RENAME instead.  
Anyway, what happens is the statement hangs on wait event 'library cache pin' 
for 5 minutes, then returns an ORA-04021.  This is on 10.2.0.2 under Linux 
x86-64 RHEL4.

While the statement is hanging I've run the following in a separate session:

SELECT /*+ ORDERED */ w1.sid waiting_session
     , h1.sid holding_session, w.kgllktype lock_or_pin, w.kgllkhdl address
     , DECODE(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',     
                                                          'Unknown') mode_held
     , DECODE(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',     
                                                          'Unknown') 
mode_requested
  FROM dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
 WHERE (    (    h.kgllkmod != 0 
             AND h.kgllkmod != 1 
             AND (   h.kgllkreq = 0 
                  OR h.kgllkreq = 1))
        AND (    (   w.kgllkmod = 0 
                  OR w.kgllkmod= 1) 
             AND (    w.kgllkreq != 0 
                  AND w.kgllkreq != 1)))
   AND w.kgllktype = h.kgllktype
   AND w.kgllkhdl = h.kgllkhdl
   AND w.kgllkuse = w1.saddr
   AND h.kgllkuse = h1.saddr;

... which shows that the RENAME is waiting on itself:

WAITING_SESSION HOLDING_SESSION LOCK ADDRESS          MODE_HELD MODE_REQU
--------------- --------------- ---- ---------------- --------- ---------
            465             465 Pin  00000001FC7F7A30 Share     Exclusive

The ADDRESS listed above points to the table I'm trying to rename.  I've got 2 
databases on this server that exhibit the same behavior.  I thought maybe it 
was an issue with 10.2.0.2, but I created a 3rd database and can't recreate the 
problem on it.

Any ideas of what else I could check?

Dave
-------------------------------------
Dave Herring, DBA
Acxiom Corporation
3333 Finley
Downers Grove, IL 60515
wk: 630.944.4762
<mailto:dherri@xxxxxxxxxx>
-------------------------------------
 
"When I come home from work and see those little noses pressed against the 
windowpane, then I know I am a success" - Paul Faulkner
***************************************************************************
The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be legally
privileged.

If the reader of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.

If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.

Thank You.
****************************************************************************
--
//www.freelists.org/webpage/oracle-l
RENAME and Library cache pin waits

Other related posts:
