穷而后工
成语发音
qióng ér hòu gōng
成语解释
旧时认为文人越是穷困不得志，诗文就写得越好。
成语出处
宋·欧阳修《梅圣俞诗集序》：“然则非诗之能穷人，殆穷者而后工也。”
This man is too old to remember everything in his brain. Right now, he needs a place to write down what he has studied.
2012年9月4日 星期二
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>