連結： http://blog.itpub.net/28602568/viewspace-1289390/

標題： Oracle Edit product卡死不動，引起的等待事件‘library cache pin’解決方案

註釋：
前段時間Edit product卡死不動，根據檢視v$session_wait獲取等待事件為:‘library cache pin’

關於‘library cache pin’ 參看部落格：、

本文重點介紹如果遇到‘library cache pin’ 等待的解決方案

--查詢哪些會話有關於library cache pin/library cache lock 等待事件的資訊【會話狀態／程式／ＳＩＤ／使用者．．等】

SQL> SELECT C.SPID,A.SID, A.SERIAL#, A.USERNAME, A.STATUS /*程式狀態*/,A.OSUSER,A.MACHINE,A.PROGRAM,B.ADDR

FROM V$SESSION A, X$KGLPN B,V$PROCESS C
  WHERE A.SADDR = B.KGLPNUSE
  AND C.ADDR=A.PADDR
AND B.KGLPNMOD <> 0
AND B.KGLPNHDL IN
       (SELECT P1RAW FROM V$SESSION_WAIT WHERE upper(EVENT) LIKE upper('LIBRARY CACHE%'))
   /*AND A.STATUS= 'KILLED'*/;

SPID SID SERIAL# USERNAME STATUS OSUSER MACHINE PROGRAM ADDR

----- ---- ---------- ------------------ -------- -------------------- ---------------------------------- -------------------------- ----------------

11949 963 2079 MIDTEST_3 KILLED Administrator WORKGROUP\2012-20121008 OS plsqldev.exe 00007F85060DC8B8

SQL>

若status=active，確認不適用後可以選擇用Oracle kill 或者OS kill 來釋放資源

Oracle kill

alter system kill session '963,2079' immediate;

alter system kill session '963,2079' ;

作業系統kill [由於本環境status=killed，所以直接OS kill　如下]

[root@xcd ~]# ps -ef| grep 13309
oracle 11949 1 0 13:19 ? 00:00:00 oraclekfdb (LOCAL=NO)

root 15263 15236 0 15:38 pts/0 00:00:00 grep 11949

[root@xcd ~]# kill -9 11949
[root@xcd ~]# ps -ef| grep 11949
root 15259 15236 0 15:38 pts/0 00:00:00 grep 11949

[root@xcd ~]#

SQL> select a.spid,b.sid,b.serial#,b.username from v$process a,v$session b where a.addr=b.paddr and b.status= 'KILLED';

SPID SID SERIAL# USERNAME

---------------- ---------- ------------------------------

SQL>

再重新編譯問題解決了~

--多個會話同時操作可能引起的報錯【 ORA-04021: 等待鎖定物件時發生超時】

--如果是多個的話查詢如下SQL

select * from v$access where object = 'IES_INV_MTL_OUT_APPLY' ;

select kglnaown, kglnaobj, kglnadlk

from x$kglob t, v$session_wait sw

where t.kglhdadr = sw.p1raw

and sw.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 cache%'));

此條目發表在 Oracle 分類目錄。將固定連線加入收藏夾。

Oracle Edit product卡死不動，引起的等待事件‘library cache pin’解決方案

相關文章