Oracle鎖表故障解決執行個體

Oracle鎖表故障解決執行個體

一次開發人員直接在資料庫伺服器上做delete table作業系統，由時在上班高峰運行，造成長時間執行無反應，進而把SQLPLUS用戶端關閉掉，再次運行刪除語句，造成資料庫運行緩慢。 

詢問操作過程知道了是由鎖表造成，最終處理；本文只是類比鎖表處理過程，下次遇到相同問題可以快速處理。

Oracle學習筆記：sqlplus使用者登入

sqlplus登入Oracle時ORA-01017: invalid username/password; logon denied的錯誤 

SQLPLUS中的feedback設定

1. 查看資料庫的等待事件
如果不清楚原因，可以通過先通過等待事件進行分析。
SQL> select sid,EVENT from v$session_wait where wait_class<>'Idle';
      SID EVENT 
---------- ----------------------------------------------------------------   
      158 SQL*Net message to client   
      159 enq: TX - row lock contention   
SQL>
 
2. 查看資料庫是否有鎖
SQL> select * from v$lock where block=1;
 
3. 查詢lock鎖, 看誰鎖誰
說明：BLOCK為1的行，表示資源由它鎖定。REQUEST表示需要這個鎖。
SQL> SELECT sid, id1, id2, lmode, block,request, type   
FROM V$LOCK   
WHERE id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0)   
ORDER BY id1, request;
      SID        ID1        ID2      LMODE      BLOCK    REQUEST TY 
---------- ---------- ---------- ---------- ---------- ---------- --   
      153    655397        308          6          1          0 TX   
      157    655397        308          0          0          6 TX
 
4. 查詢鎖表的SID,Serial#，使用者、開始時間，然後就可以殺掉該進程了。
SQL> select t1.sid,t1.serial#,t1.username,t1.logon_time from v$session t1,v$locked_object t2 where t1.sid = t2.session_id order by t1.logon_time ;
      SID    SERIAL# USERNAME                      LOGON_TIME 
---------- ---------- ------------------------------ -------------------   
      153        12 ABC                            2014-11-17 09:19:33   
      157        106 ABC                            2014-11-17 09:54:24
 
5. 可以進一步查詢出SID，SPID等資訊
SQL> select s.username, s.oSUSEr, s.sid, s.serial#, p.spid, s.program,s.STATUS 
from v$session s,v$process p 
where s.paddr = p.addr and s.username is not null;
USERNAME                      OSUSER                                SID    SERIAL# SPID        PROGRAM                                          STATUS 
------------------------------ ------------------------------ ---------- ---------- ------------ ------------------------------------------------ --------   
ABC                            oracle                                153        12 4290        sqlplus@oradb (TNS V1-V3)                        INACTIVE   
SYS                            oracle                                150        12 4417        sqlplus@oradb (TNS V1-V3)                        ACTIVE   
ABC                            oracle                                157        119 4830        sqlplus@oradb (TNS V1-V3)                        ACTIVE
要麼在oracle進行殺掉，以及在作業系統下殺掉，對於status為killed的語句，應用通過作業系統kill命令才能最終最快殺掉。
alter system kill session  '153,12'; 
alter system kill session  '157,119';
或作業系統中
kill -9 4290 
kill -9 4830
 
6.  查詢被鎖的表的SID及相關資源
說明：出被鎖的表及SQL，還是要結合上面的查詢進行手工處理, 也可以通過SID找到SQL語句。
set pagesize 999; 
set linesize 200;   
col PROGRAM for a25;   
col TERMINAL for a10;
SQL> select s.status,s.sid,s.serial#,p.spid, 
      s.last_call_et as exec_seconds,t.sql_text as curr_sql 
from gv$session s,v$process p,v$instance i,v$lock k, v$sqltext t 
where s.paddr = p.addr 
and s.type != 'BACKGROUND' 
and s.lockwait = k.kaddr 
and s.sql_hash_value = t.hash_value 
and s.username is not null; 
STATUS          SID    SERIAL# SPID        EXEC_SECONDS CURR_SQL   
-------- ---------- ---------- ------------ ------------ ----------------------------------------------------------------   
ACTIVE          159        28 4072                1285 update test_lock set name='aa4' where id = 1
SQL>