常用定位library cache lock的方法
經常看到PUB上有兄弟說什么LIBRARY CACHE LOCK和PIN的錯誤不知道該如何處理，而且定位不到問題出在哪里,我來說幾句吧,以我工作上的經驗，希望能對大家有用
一般來說,這類錯誤是因為在包或過程被頻繁調用的過程中，DDL語句引起的，那我們該怎么處理呢?其實我們可以這樣來操作來查原因，老板要的一般都是為什么產生這個故障和誰操作導致的這個故障，特別是故障處理好后，這個問題就要回答老板了。
1、預先在數據庫中建立DDL級的觸發器,我認為這個是必要的,因為這個對生產影響不大，但是卻可以讓我們監控到不少有用的信息.，比如記錄在abc表中，可以記錄登陸用戶，操作語句，操作時間等等信息。
2、在數據庫中出現大量的libriary cache lock 的等待事件的時候，系統出現嚴重的問題了，我們可以立即從這個時間點左右著手，比如12日21日中午12點到12點半之間出問題，如下語句
select * from dba_objects where
last_ddl_time>to_date('20071221 12:00:00','yyyymmdd hh24:mi:ss')
AND last_ddl_time<=to_date('200712 12:30:00','yyyymmdd hh24:mi:ss')
and (object_type like '%PACK%' or object_type like 'FUNCTION' OR object_type='PROCEDURE')
AND STATUS='INVALID'
order by last_ddl_time desc
其實通過這個基本上就發現是什么問題了，基本上就只會有一兩個對象比如包BBB失效
3、然后找包關聯的對象，是否在我們的觸發器記錄的表中有記錄，接著執行如下語句（切記，這個記錄DDL動作的語句發揮作用了）
select * from abc where ddl_time>to_date('20071221 12:00:00','yyyymmdd hh24:mi:ss')
AND ddl_time<=to_date('200712 12:30:00','yyyymmdd hh24:mi:ss')
and schema_object in (SELECT referenced_name FROM DBA_DEPENDENCIES WHERE NAME='BBB' )
ORDER BY DDL_time desc
（請注意，這個BBB就是上面我查出來的，舉例說比如失效的包）
這樣查出來的，絕對就是引起這次事故的罪魁禍首的動作了。（ddl_time和 schema_object 是abc表的字段，記錄了登陸者操作DDL的時間和對象）
以上方式是我在工作中經常采用的，很好用，一般不會有問題。
當
然我上面并沒有說明解決問題的方法，解決問題的方法是如下。但是有的時候發現問題原因，追究問題原因是非常非常重要的，可以避免下次再發生，當然通過
DUMP
systemstate等方式，比較復雜，我的這個思路操作起來應該比較簡便，很明了。另外，建立DDL級的觸發器，個人認為是必須的！所以上面的方法我
想說出來，希望對大家有用！
解決問題的方法步驟
1、查看具體產生library cache lock 的對象，比如不哪些包和存儲過程
SELECT KGLNAOWN,KGLNAOBJ
FROM x$kglob
WHERE kglhdadr in( select P1RAW from v$session_wait where event like 'library cache%');
2、 查看具體是那些用戶做了這個操作導致 library cache lock
select sid, program ,machine from v$session where paddr in (
SELECT s.paddr
FROM x$kglpn p, v$session s
WHERE p.kglpnuse=s.saddr(+) AND p.kglpnmod <> 0
and kglpnhdl in ( select p1raw from v$session_wait where event in
('library cache pin','library cache lock' ,'library cache load
lock') ) );
3、、以下語句用來殺掉會話（前面查看，然后到這步是決定是否要殺掉進程解決這個問題）
select 'kill -9 '||spid from v$process where addr in (
SELECT s.paddr
FROM x$kglpn p, v$session s
WHERE p.kglpnuse=s.saddr --AND p.kglpnmod <> 0
and kglpnhdl in ( select p1raw from v$session_wait where event in (' library cache pin','library cache lock' ) ) );
看過我這帖子的兄弟希望能補充補充，看看有沒有說的不對的地方，或者說考慮不全面的地方，謝謝！
附：DDL觸發器的語句
CREATE OR REPLACE TRIGGER tr_trace_ddl
AFTER ddl
ON database
DECLARE
SQL_TEXT ORA_NAME_LIST_T;
STATE_SQL VARCHAR2(4000); --DDL$TRACE.DDL_SQL%TYPE;
V_ERR_INFO VARCHAR2(200);
BEGIN
FOR I IN 1 .. ORA_SQL_TXT(SQL_TEXT) LOOP
STATE_SQL := STATE_SQL || SQL_TEXT(I);
END LOOP;
INSERT INTO SYSTEM.ABC
(LOGIN_USER,
AUDSID,
IPADDRESS,
SCHEMA_USER,
SCHEMA_OBJECT,
DDL_TIME,
DDL_SQL)
VALUES
(ORA_LOGIN_USER,
USERENV('SESSIONID'),
SYS_CONTEXT('userenv', 'ip_address'),
ORA_DICT_OBJ_OWNER,
ORA_DICT_OBJ_NAME,
SYSDATE,
STATE_SQL);
EXCEPTION
WHEN OTHERS THEN
V_ERR_INFO := SUBSTRB(SQLERRM, 1, 198);
END TR_TRACE_DDL;
3、、以下語句用來殺掉會話（前面查看，然后到這步是決定是否要殺掉進程解決這個問題）
目的找出spid ,也可以用下面的sql:
SELECT spid
FROM v$process p, v$session s
WHERE p.addr = s.paddr
AND s.SID IN (SELECT sid
FROM v$session_wait b
WHERE b.EVENT in (' library cache pin','library cache lock' ) )
前提是sesion 沒有被 killed 掉, 如果被killed , v$session.paddr <> v$process.addr.
