Peter Lin-被動投資紀錄

1. 查看哪些Process被鎖住
select t2.username,t2.sid,t2.serial#,t2.logon_time

from v$locked_object t1,v$session t2

where t1.session_id=t2.sid order by t2.logon_time; 

2.查看對應執行的是哪一個SQL語句(會提示輸入SID參數，就是上一步查詢出來 的SID)
select sql_text from v$session a,v$sqltext_with_newlines b
 where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value
 and a.sid=&sid order by piece;

 3.殺死對應的Process(上述的sid與serial#參數傳入)
  alter system kill session 'sid,serial#';

如出現：

Ora-00031：session marked for kill之訊息，請再進行以下步驟

4. 取得語句的Process代號
select spid,osuser,s.program from v$session s,v$process p
where s.paddr=p.addr and s.sid=&sid;

5.在OS中殺死該Process
 unix系統，以root身份執行以下命令
 # kill -9 spid(即第4步查詢出來的Process代號spid)

Reference:http://tc.itkee.com/database/detail-1dda.html