Library cache lock 解決方式

>>1 讓Oracle dump trace file 
SQL> ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 8'; 

>>2 dump 的目錄 
SQL> show parameter user_dump_dest 

NAME                                 TYPE       VALUE 
------------------------------------ ---------- ------------------------------ 
user_dump_dest                       string     /opt/oracle/diag/rdbms/dwp01/dwp01/trace 

SQL> exit 

>>3 去alert file找出trace file檔名 
oracle@aot-dwp01:/opt/oracle/diag/rdbms/dwp01/dwp01/trace$ tail -100 alert_dwp01.log 

... 
... 
... 
Tue Sep 04 11:40:30 2012 
Thread 1 advanced to log sequence 135140 (LGWR switch) 
  Current log# 2 seq# 135140 mem# 0: /oracle/dwp01/redo02.log 
Tue Sep 04 11:42:55 2012 
System State dumped to trace file /opt/oracle/diag/rdbms/dwp01/dwp01/trace/dwp01_ora_13383.trc <<剛剛的動作，讓Oracle dump此trace file 

>>4 找出被block pid，再去trace file 用vi找出 PROCESS <pid> 
SQL > select sid from v$session_wait where event = 'library cache lock'; <<找出被librarya cache lock的session 
SQL > SELECT PID FROM V$PROCESS WHERE ADDR = (SELECT PADDR FROM V$SESSION WHERE SID='355');  <<找出上一行session的pid 

>>5 找到 PROCESS <pid>後，用 waiting for字眼找出block別人的handle address (此次例子是handle address=0x158bf4188) 
     0: waiting for 'library cache lock' 
        handle address=0x158bf4188, lock address=0x12bf22a60, 100*mode+namespace=0x1888a00010003 
        wait_id=123 seq_num=124 snap_id=1 
        wait times: snap=6 min 47 sec, exc=6 min 47 sec, total=6 min 47 sec 
        wait times: max=infinite, heur=6 min 47 sec 
        wait counts: calls=137 os=137 
        in_wait=1 iflags=0x15a2 

>>6 用vi找出哪一個PROCESS擁有此handle address，以下為此次例子，並往上用vi移動，找出是哪一個PROCESS <pid> 
    Dumping one waiter: 
      inst: 1, sid: 355, ser: 23 
      wait event: 'library cache lock' 
        p1: 'handle address'=0x158bf4188 
        p2: 'lock address'=0x12bf22a60 
        p3: '100*mode+namespace'=0x1888a00010003 
      row_wait_obj#: 4294967295, block#: 0, row#: 0, file# 0 
      min_blocked_time: 407 secs, waiter_cache_ver: 55785 
    Wait State: 
      fixed_waits=0 flags=0x22 boundary=(nil)/-1 

>>7 找出其os process id後，用os指令 kill -9 <spid> 解決 
select a.spid 
  from v$process a,v$session b 
 where a.pid = 87 
   and a.addr = b.paddr 

>>8 kill -9 <spid> 
oracle@aot-dwp01:~$ kill -9 <spid>