Oracle鎖表故障處理一例,Oracle鎖表故障處理
一次開發人員直接在資料庫伺服器上做delete table作業系統,由時在上班高峰運行,造成長時間執行無反應,進而把SQLPLUS用戶端關閉掉,再次運行刪除語句,造成資料庫運行緩慢。
詢問操作過程知道了是由鎖表造成,最終處理;本文只是類比鎖表處理過程,下次遇到相同問題可以快速處理。
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>