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

From: Tim Gorman <tim_at_evdbt.com>
Date: Wed, 26 Oct 2011 21:27:53 +0000
Message-ID: <W6416525475245921319664473_at_webmail64>

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 RHEL5stored proc a isbeginclone_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 alibrary cache pin and it does not show any blocking session. I am wonderingwhy i am even seeing AE type locks?any help in understanding is greatly appreciated.ThanksDeenSELECT * FROM GV$LOCK WHERE INST_ID=3 AND SID%13; INST_ID ADDR KADDR SID TY ID1ID2 LMODE REQUEST CTIME BLOCK---------- ---------------- ---------------- ---------- -- -------------------- ---------- ---------- ---------- ---------- 3 0000000906A7C5E8 0000000906A7C640 2513 AE0 1 4 0 2411 2 3 0000000906A8A8A8 0000000906A8A900 2513 AE42769047 0 4 0 6895 2select object_name,owner,object_type from dba_objects whereobject_idB769047;OBJECT_NAMEOWNER OBJECT_TYPE-------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------ORA$BASES
 YS EDITIONSELECT SID, SERIAL#,BLOCKING_SESSION, BLOCKING_INSTANCE, PROGRAM, EVENT FROMGV$SESSION WHERE sid%13 AND INST_ID=3; SID SERIAL# BLOCKING_SESSION BLOCKING_INSTANCEPROGRAM EVENT---------- ---------- ---------------- ----------------------------------------------------------------- -------2513 24597Toad.exe library cache pin--http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Wed Oct 26 2011 - 16:27:53 CDT