用户来电在使用plsqldev工具编译存储过程时卡住，远程查看编译时对应等待事件是library cache pin，于是根据此library cache pin等待事件P1RAW找出持有此资源的会话并KILL后，可以正常编译。
对于library cache pin等待事件找出资源持有者的方法是：
a.查出library cache pin等待的P1RAW值：
select sid, event, p1raw from v$session_wait where event = ‘library cache pin’;
b.根据查出的P1RAW值=288822D4，进行下一步查询
select kglnaown, kglnaobj from x$kglob where kglhdadr = ‘288822D4’;
注意:如果是RAC环境，可能还需要根据b.步骤查出的kglnaobj值，在其它实例上进行查询：
select kglhdadr, kglnaown, kglnaobj from x$kglob where kglnaobj = ‘DUMMY’;
c.根据b.步骤查出的glhdadr值来查持有资源的会话–RAC需要注意在相应实例上查询
select sid, serial#, sql_text from dba_kgllock w, v$session s, v$sqlarea a
where w.kgllkuse = s.saddr and w.kgllkhdl=’28577AD8′
and s.sql_address = a.address and s.sql_hash_value = a.hash_value;
或者：
SELECT s.sid, kglpnmod “Mode”, kglpnreq “Req” FROM x$kglpn p, v$session s
WHERE p.kglpnuse=s.saddr AND kglpnhdl=’&P1RAW’
d.查出持有library cache pin等待对应的会话信息后，是kill还是等就交给业务和用户来选择了。
–关于library cache pin的参数含义、Mode & Namespace等更多信息，参考MOS文档：
WAITEVENT: “library cache pin” Reference Note (文档 ID 34579.1)
How to Find the Blocker of the ‘library cache pin’ in a RAC environment? (文档 ID 780514.1)
我的处理过程如下：
首先plsqldev工具开一个命令行的窗口，查出当前会话的SID；然后把存储过程创建语句执行起来。
1.查出编译存储过程的会话的等待事件信息
SQL> select machine,inst_id,sid,event from gv$session where sid=1776;
MACHINE INST_ID SID EVENT
---------- ---------- ---------- -------------------------
WORKGROUP\ 1 1776 library cache pin
2.查询此等待事件的P1/P2/P3值，这个等待事件其实只需要P1
SQL>selectsid,seq#,event,p1,p1raw from v$session_wait where event like 'library%';
SIDSEQ# EVENT P1 P1RAW
-----------------------------------------------------------------------
1776137librarycachepin5.0440E+170700000379E52548
根据P1RAW值查出相应的等待事件在等待的对象信息
SQL>selectaddr,kglhdadr,kglhdpar,kglnaown,kglnaobj,kglnahsh,kglhdobjfromsys.x$kglobwherekglhdadr='0700000379E52548';
ADDRKGLHDADRKGLHDPARKGLNAOWN
------------------------------------------------
KGLNAOBJ
-----------
KGLNAHSHKGLHDOBJ
--------------------------
00000001106312F00700000379E525480700000379E52548JTDATA
JOB_YWBKTJ
833204742070000038D9D87A0
3.再根据P1RAW值关联查出持有此资源的会话信息：
SQL> select a.PADDR,
2 a.sid,
3 a.SERIAL#,
4 a.PROGRAM,
5 a.SQL_ADDRESS,
6 a.STATUS,
7 a.SQL_HASH_VALUE,
8 b.addr,
9 b.kglpnadr,
10 b.kglpnuse,
11 b.kglpnses,
12 b.kglpnhdl,
13 b.kglpnlck,
14 b.kglpnmod,
15 b.kglpnreq
16 from x$kglpn b, v$session a
17 where a.SADDR = b.kglpnuse
18 and b.kglpnhdl = '0700000379E52548'
19 and b.kglpnmod <> 0
20 ;
PADDR SID SERIAL# PROGRAM SQL_ADDRESS STATUS SQL_HASH_VALUE ADDR KGLPNADR KGLPNUSE
---------------- ---------- ---------- ------------------------------------------------ ---------------- -------- -------------- ---------------- ---------------- ----------------
KGLPNSES KGLPNHDL KGLPNLCK KGLPNMOD KGLPNREQ
---------------- ---------------- ---------------- ---------- ----------
07000003FE8BC8E8 821 6915 oracle@aaadb1 (J002) 07000003FD5DC8E0 ACTIVE 3746164040 000000011067E728 07000003FB58E498 07000003F4C10310
07000003F4C10310 0700000379E52548 07000003FB58E590 2 0
4.查询持有资源的SID在做什么？
SQL>selectevent,sidfrom v$session_wait wheresid=821;
EVENT SID
-----------------------------------
single-taskmessage 821
SQL>select b.spid,a.sid,a.username,a.program,a.machine,C.SQL_ID
2 fromv$sessiona,v$processb,V$SQLTEXTC
3 wherea.paddr=b.addrANDA.SQL_HASH_VALUE=C.HASH_VALUEanda.sid=821;
SPID SIDUSERNAME PROGRAM MACHINE SQL_ID
---------------------------------------------------------------------------------------------------------------------
1044886 821AAADATA oracle@aaadb1(J002) aaadb1 auarc9bgnmva8
1044886 821AAADATA oracle@aaadb1(J002) aaadb1 auarc9bgnmva8
1044886 821AAADATA oracle@aaadb1(J002) aaadb1 auarc9bgnmva8
1044886 821AAADATA oracle@aaadb1(J002) aaadb1 auarc9bgnmva8
SQL>selectsql_textfromv$sqltextwheresql_id='auarc9bgnmva8'orderbypiece;
SQL_TEXT
----------------------------------------------------------------
INSERTINTOB2aaaaaaa_YD@B2aaaaaaa(YD^^^^^
SRQ,CKID,B2aaaaaaa_YDID)(SELECT YDHFROMW2E_YDWHEREYDHIN(SELECTYDHFROMW2
E_YDMINUSSELECTYDHFROMB2aaaaaaa_YD@B2aaaaaaa))
5.解决：沟通后KILL此会话，存储过程顺利编译完成。
$ps-ef|grep1044886
oracle1044886 1 0 Nov22 - 0:11ora_j002_hello1
oracle25852502581190 016:36:09 pts/2 0:00grep1044886
$kill-91044886
$ps-ef|grep1044886
oracle26264242581190 016:36:24 pts/2 0:00grep1044886
