Oracle 死锁的检测查询及处理
-- 死锁查询语句 SELECT bs.username "Blocking User",bs.username "DB User",ws.username "Waiting User",bs.SID "SID",ws.SID "WSID",bs.serial# "Serial#",bs.sql_address "address",bs.sql_hash_value "Sql hash",bs.program "Blocking App",ws.program "Waiting App",bs.machine "Blocking Machine",ws.machine "Waiting Machine",bs.osuser "Blocking OS User",ws.osuser "Waiting OS User",ws.serial# "WSerial#",DECODE (wk.TYPE,'MR','Media Recovery','RT','Redo Thread','UN','USER Name','TX','Transaction','TM','DML','UL','PL/SQL USER LOCK','DX','Distributed Xaction','CF','Control FILE','IS','Instance State','FS','FILE SET','IR','Instance Recovery','ST','Disk SPACE Transaction','TS','Temp Segment','IV','Library Cache Invalidation','LS','LOG START OR Switch','RW','ROW Wait','SQ','Sequence Number','TE','Extend TABLE','TT','Temp TABLE',wk.TYPE ) lock_type,DECODE (hk.lmode,'None',1,'NULL',2,'ROW-S (SS)',3,'ROW-X (SX)',4,'SHARE',5,'S/ROW-X (SSX)',6,'EXCLUSIVE',TO_CHAR (hk.lmode) ) mode_held,DECODE (wk.request,TO_CHAR (wk.request) ) mode_requested,TO_CHAR (hk.id1) lock_id1,TO_CHAR (hk.id2) lock_id2,DECODE (hk.BLOCK,'NOT Blocking',/**//* Not blocking any other processes */ 1,'Blocking',/**//* This lock blocks other processes */ 2,'Global',/**//* This lock is global,so we can't tell */ TO_CHAR (hk.BLOCK) ) blocking_others FROM v$lock hk,v$session bs,v$lock wk,v$session ws WHERE hk.BLOCK = 1 AND hk.lmode != 0 AND hk.lmode != 1 AND wk.request != 0 AND wk.TYPE(+) = hk.TYPE AND wk.id1(+) = hk.id1 AND wk.id2(+) = hk.id2 AND hk.SID = bs.SID(+) AND wk.SID = ws.SID(+) AND (bs.username IS NOT NULL) AND (bs.username <> 'SYSTEM') AND (bs.username <> 'SYS') ORDER BY 1; --查询发生死锁的select语句
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 username,lockwait,status,machine,program from v$session where sid
in (select session_id from v$locked_object) 如果有输出的结果,则说明有死锁,且能看到死锁的机器是哪一台。字段说明:
四、死锁的解决方法
1)查找死锁的进程: sqlplus "/as sysdba" (sys/change_on_install)SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID; 2)kill掉这个死锁的进程: alter system kill session ‘sid,serial#’; (其中sid=l.session_id)
3)如果还不能解决: select pro.spid from v$session ses,v$process pro where ses.sid=XX and ses.paddr=pro.addr; 其中sid用死锁的sid替换: exit 其中spid是这个进程的进程号,kill掉这个Oracle进程。 转自:http://sungang-82.iteye.com/blog/310781 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |