-------------------------------------->>
---------------------------->>
接着通過sqldevelpdev用戶端查詢有沒有鎖等待之類會話事件,果然有,而且是兩個session持有tx鎖,然後通過下面的sql查詢從oracle和linux級别kill掉了相應session,以為風波就此平靜,結果過了不到一分鐘查詢又出現,隻不過這次隻有一個session持有tx鎖,于是就去查找對應的sql_txt,找到後發現是個同僚寫的存儲過程,定時任務,當時正在運作,讓其确認下是不是任務執行出問題了,結果一查,是程式問題,造成的死循環,它會批量發起會話,kill一個後接着又鎖,循環反複,後來他改了下程式後重新運作,一切恢複通暢.
--查詢死鎖
select sess.sid, sess.serial#, lo.oracle_username, lo.os_user_name, ao.object_name, lo.locked_mode
from v$locked_object lo, dba_objects ao,v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid;
--oracle級别kill session
alter system kill session '1627,1';
alter system kill session '1564,64740';
--查詢目前連接配接會話
select s.value,s.sid,a.username,a.machine from v$sesstat s,v$statname n,v$session a
where n.statistic#=s.statistic# and name='session pga memory' and s.sid=a.sid and a.sid=1626
order by s.value;
--查詢造成死鎖的sql語句
select a.sid, a.username, s.sql_text from v$session a, v$sqltext s
where a.sql_address = s.address and a.sql_hash_value = s.hash_value and a.sid=1626
order by a.username, a.sid, s.piece;
--造成鎖等待的操作内容
begin
flt_com.p_line_relation_change(:a0,:b0,:c0,:d0,:e0,:ret_errorcode,:ret_errorname);
end;
--通過sid查找pid,進而通過系統級别kill
select spid, osuser, s.program from v$session s,v$process p where s.paddr=p.addr and s.sid=1605;
--伺服器級别kill
kill -9 spid
--------------------------------over game
最新内容請見作者的github頁:http://qaseven.github.io/