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@(protected)>
-------------------------------------
?
"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.
****************************************************************************
--
http://www.freelists.org/webpage/oracle-l
