library cache pin & library cache lock

--library cache pin & library cache lock
library cache pin:query P1RAW  of v$session_wait which hash value of object
--X$KGLOB--[K]ernel [G]eneric [L]ibrary Cache Manager [OB]ject
--KGLNAHSH: Hash Value of the object
select ADDR,KGLHDADR,KGLHDPAR,KGLNAOWN,KGLNAOBJ,KGLNAHSH,KGLHDOBJ
from X$KGLOB
where KGLHDADR ='&hashvalue'
--X$KGLPN--[K]ernel [G]eneric [L]ibrary Cache Manager object [P]i[N]s
--获得持有等待对象的session信息
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 = '&hashvalue' and b.KGLPNMOD<>0

====================================================
1.获得Library Cache Pin等待的对象
SELECT addr, kglhdadr, kglhdpar, kglnaown, kglnaobj, kglnahsh, kglhdobj
  FROM x$kglob
 WHERE kglhdadr IN
       (SELECT p1raw FROM v$session_wait WHERE event LIKE 'library%')

2.获得持有等待对象的session信息
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.kglpnmod <> 0
   AND b.kglpnhdl IN
       (SELECT p1raw FROM v$session_wait WHERE event LIKE 'library%')

3.获得持有对象用户执行的代码
SELECT sql_text
  FROM v$sqlarea
 WHERE (v$sqlarea.address, v$sqlarea.hash_value) IN
       (SELECT sql_address, sql_hash_value
          FROM v$session
         WHERE SID IN (SELECT SID
                         FROM v$session a, x$kglpn b
                        WHERE a.saddr = b.kglpnuse
                          AND b.kglpnmod <> 0
                          AND b.kglpnhdl IN
                              (SELECT p1raw
                                 FROM v$session_wait
                                WHERE event LIKE 'library%')))