oracle死锁彻底解决办法
检查死锁及处理
1）
select sid,serial#,username,SCHEMANAME,osuser,MACHINE,
terminal,PROGRAM,owner,object_name,object_type,o.object_id
from dba_objects o,v$locked_object l,v$session s
where o.object_id=l.object_id and s.sid=l.session_id;
2）
alter system kill session '&sid,&serial#';
3）
若上方命令不行换OS杀死进程
select spid, osuser, s.program
from v$session s,v$process p
where s.paddr=p.addr and s.sid=10;
4）
在Linux上，用root身份执行命令:
ps -ef|grep spid
-- #kill -9 12345（即第3步查询出的spid）
延伸
-- 查看死锁ID
select username,lockwait,status,machine,program from v$session where sid
in (select session_id from v$locked_object);
/*查看死锁语句*/
select sql_text from v$sql where hash_value in
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object));
--历史死锁查看
SELECT
h.*
FROM
dba_hist_active_sess_history h
WHERE
h.event = 'enq: TX - row lock contention';