用户来电在使用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> select sid,seq#,event,p1,p1raw from v$session_wait where event like 'library%';
SID SEQ# EVENT P1 P1RAW
---------- ---------- ------------------------- ---------- ----------------
1776 137 library cache pin 5.0440E+17 0700000379E52548
根据P1RAW值查出相应的等待事件在等待的对象信息
SQL> select addr,kglhdadr,kglhdpar,kglnaown,kglnaobj,kglnahsh,kglhdobj from sys.x$kglob where kglhdadr='0700000379E52548';
ADDR KGLHDADR KGLHDPAR KGLNAOWN
---------------- ---------------- ----------------
KGLNAOBJ
-----------
KGLNAHSH KGLHDOBJ
---------- ----------------
00000001106312F0 0700000379E52548 0700000379E52548 JTDATA
JOB_YWBKTJ
833204742 070000038D9D87A0
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 (J002) 07000003FD5DC8E0 ACTIVE 3746164040 000000011067E728 07000003FB58E498 07000003F4C10310
07000003F4C10310 0700000379E52548 07000003FB58E590 2 0
4.查询持有资源的SID在做什么？
SQL> select event,sid from v$session_wait where sid=821;
EVENT SID
------------------------- ----------
single-task message 821
SQL> select b.spid,a.sid,a.username,a.program,a.machine,C.SQL_ID
2 from v$session a,v$process b ,V$SQLTEXT C
3 where a.paddr=b.addr AND A.SQL_HASH_VALUE=C.HASH_VALUE and a.sid=821;
SPID SID USERNAME PROGRAM MACHINE SQL_ID
------------------------ ---------- ------------ ------------------------------------------------ ---------- -------------
1044886 821 AAADATA (J002) aaadb1 auarc9bgnmva8
1044886 821 AAADATA (J002) aaadb1 auarc9bgnmva8
1044886 821 AAADATA (J002) aaadb1 auarc9bgnmva8
1044886 821 AAADATA (J002) aaadb1 auarc9bgnmva8
SQL> select sql_text from v$sqltext where sql_id='auarc9bgnmva8' order by piece;
SQL_TEXT
----------------------------------------------------------------
INSERT INTO (YD^^^^^
SRQ, CKID, B2aaaaaaa_YDID) (SELECT YDH FROM W2E_YD WHERE YDH IN (SELECT YDH FROM W2
E_YD MINUS SELECT YDH FROM ))
4.解决：沟通后KILL此会话，存储过程顺利编译完成。
$ ps -ef|grep 1044886
oracle 1044886 1 0 Nov 22 - 0:11 ora_j002_hello1
oracle 2585250 2581190 0 16:36:09 pts/2 0:00 grep 1044886
$ kill -9 1044886
$ ps -ef|grep 1044886
oracle 2626424 2581190 0 16:36:24 pts/2 0:00 grep 1044886
