Re: library cache pin while trying to recreate a stored proc

From: dd yakkali <dd.yakkali_at_gmail.com>
Date: Wed, 26 Oct 2011 15:26:57 -0700
Message-ID: <CAJ-BTpy33ncVA7Uq0gf+cLwM8Hodsc-7iKKn+Pb6t=rnV48voA_at_mail.gmail.com>

Tim,
Thanks for the reply. I ran the query and found that there are a lot of them with that id1 and I do not think they are all executing that procedure. Again i am more curious about AE type locks, I read they are Edition based lock types and we are not using that edition based features unless oracle uses them internally

SELECT * FROM GV$LOCK WHERE ID1=42769047;

   INST_ID ADDR             KADDR                   SID TY        ID1
ID2      LMODE    REQUEST      CTIME      BLOCK
---------- ---------------- ---------------- ---------- -- ----------
---------- ---------- ---------- ---------- ----------
        17 0000000906A747F0 0000000906A74848       1564 AE
42769047          0          4          0        657          2
        17 0000000906A74A60 0000000906A74AB8       2502 AE
42769047          0          4          0         58          2
        17 0000000906A6DD90 0000000906A6DDE8       2586 AE
42769047          0          4          0     637946          2
        17 0000000906A78BD8 0000000906A78C30       2599 AE
42769047          0          4          0     636456          2
        17 0000000906A77840 0000000906A77898       2600 AE
42769047          0          4          0     440984          2
        17 0000000906A76BF8 0000000906A76C50       2610 AE
42769047          0          4          0     637990          2
        12 0000000906A7B9B8 0000000906A7BA10       1908 AE
42769047          0          4          0       1033          2
        12 0000000906A75C58 0000000906A75CB0       2219 AE
42769047          0          4          0        609          2
        12 0000000906A7D4B8 0000000906A7D510       2258 AE
42769047          0          4          0       5423          2
        12 0000000906A82B68 0000000906A82BC0       2268 AE
42769047          0          4          0         31          2
        12 0000000906A828F8 0000000906A82950       2335 AE
42769047          0          4          0       8143          2

...
..

         1 0000000906A7E940 0000000906A7E998       2603 AE
42769047          0          4          0       2515          2
         1 0000000906A7E6B8 0000000906A7E710       2610 AE
42769047          0          4          0     638417          2

452 rows selected.

On Wed, Oct 26, 2011 at 2:27 PM, Tim Gorman <tim_at_evdbt.com> wrote:

> DD,
>
> Procedure CLONE_X is pinned because it is referenced by procedure A, which
> you are currently executing.
>
> To find the sessions blocked by your TOAD session 2513, run the following
> query based on the TYPE and ID1 values shown in your output...
>
> select * from gv$lock where type = 'AE' and ID1 = 0;
> select * from gv$lock where type = 'AE' and ID1 = 42769047;
>
> Hope this helps...
>
> -Tim
>
> -----Original Message-----
> *From:* dd yakkali [mailto:dd.yakkali_at_gmail.com]
> *Sent:* Wednesday, October 26, 2011 03:11 PM
> *To:* oracle-l_at_freelists.org
> *Subject:* library cache pin while trying to recreate a stored proc
>
> 11.1.0.7 on RHEL5 stored proc a is begin clone_x; copy_x; do_blah_x; <-
> Currently executing this. .... end; I am trying to recreate clone_x in a
> different session and i am is getting a library cache pin and it does not
> show any blocking session. I am wondering why i am even seeing AE type
> locks? any help in understanding is greatly appreciated. Thanks Deen SELECT
> * FROM GV$LOCK WHERE INST_ID=3 AND SID=2513; INST_ID ADDR KADDR SID TY ID1
> ID2 LMODE REQUEST CTIME BLOCK ---------- ---------------- ----------------
> ---------- -- ---------- ---------- ---------- ---------- ----------
> ---------- 3 0000000906A7C5E8 0000000906A7C640 2513 AE 0 1 4 0 2411 2 3
> 0000000906A8A8A8 0000000906A8A900 2513 AE 42769047 0 4 0 6895 2 select
> object_name,owner,object_type from dba_objects where object_id=42769047;
> OBJECT_NAME OWNER OBJECT_TYPE
> --------------------------------------------------------------------------------------------------------------------------------
> ------------------------------ ------------------- ORA$BASE SYS EDITION
> SELECT SID, SERIAL#,BLOCKING_SESSION, BLOCKING_INSTANCE, PROGRAM, EVENT FROM
> GV$SESSION WHERE sid=2513 AND INST_ID=3; SID SERIAL# BLOCKING_SESSION
> BLOCKING_INSTANCE PROGRAM EVENT ---------- ---------- ----------------
> ----------------- ------------------------------------------------ -------
> 2513 24597 Toad.exe library cache pin --
> http://www.freelists.org/webpage/oracle-l
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Oct 26 2011 - 17:26:57 CDT