library cache pin 处理

时间：2010-06-02 来源：drew

select sid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,state from v$session_wait where event like 'library%';
SQL> set linesize 999
SQL> /

SID SEQ# EVENT P1 P1RAW P2 P2RAW P3 P3RAW STATE
---------- ---------- ---------------------------------------------------------------- ---------- ---------------- ---------- ---------------- ---------- ---------------- -------------------
1297 65 library cache pin 1.8284E+10 0000000441CDF010 1.7975E+10 000000042F6103A8 301 000000000000012D WAITING

select ADDR,KGLHDADR,KGLHDPAR,KGLNAOWN,KGLNAOBJ,KGLNAHSH,KGLHDOBJ from X$KGLOB where KGLHDADR ='0000000441CDF010';

ADDR KGLHDADR KGLHDPAR KGLNAOWN
---------------- ---------------- ---------------- ----------------------------------------------------------------
KGLNAOBJ
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
KGLNAHSH KGLHDOBJ
---------- ----------------
00002B9EA8A91CD0 0000000441CDF010 0000000441CDF010 VGOP
PROC_TDCLA_SRVEXPTOLOCAL
3431019715 00000004251CB0F0

select a.sid,a.username,a.program,b.addr,b.KGLPNADR,b.KGLPNUSE,b.KGLPNSES,b.KGLPNHDL,b.kGLPNLCK, b.KGLPNMOD, b.KGLPNREQ
from v$session a,x$kglpn b
where a.saddr=b.kglpnuse and b.kglpnhdl = '0000000441CDF010' and b.KGLPNMOD<>0;

SID USERNAME                       PROGRAM                                          ADDR             KGLPNADR         KGLPNUSE         KGLPNSES         KGLPNHDL         KGLPNLCK           KGLPNMOD   KGLPNREQ
---------- ------------------------------ ------------------------------------------------ ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------- ----------
      1282 VGOP                           plsqldev.exe                                     00002B9EA8A92FB0 000000042E779E68 000000043C460088 000000043C460088 0000000441CDF010 000000042F5C4050          2            0


     再根据sid 查出sql_id,去查v$sqlarea 知道目前运行的脚本