六
05
扑捉数据库死锁
declare tmp_id1 number(22); tmp_id2 number(22); lk_sid number(22); tmp_mac varchar2(64); fl boolean; begin fl := false; for c1 in (select sid, serial#, username, machine, lockwait from v$session where lockwait is not null) loop if fl = false then dbms_output.put_line('Wait SID,Wait Serial,Wait User,Wait Machine,Lock SID,Lock Machine,Lock Mode,Lock Obj'); end if; fl := true; select id1, id2 into tmp_id1, tmp_id2 from v$lock where sid = c1.sid and kaddr = c1.lockwait; for c2 in (select sid, decode(id2, 0, id1, 1) obj, decode(lmode,1,'No Lock', 2,'Row Share', 3,'Row Exclusive', 4,'Share', 5,'Share Row Exclusive', 6,'Exclusive','NONE') lmode from v$lock where sid != c1.sid and id1 = tmp_id1 and id2 = tmp_id2 and block = '1') loop select machine into tmp_mac from v$session where sid = c2.sid; dbms_output.put_line(to_char(c1.sid) || ',' || to_char(c1.serial#) || ',' || c1.username || ',' || c1.machine || ',' || to_char(c2.sid) || ',' || tmp_mac || ',' || c2.lmode || ',' || c2.obj); end loop; end loop; if not fl then dbms_output.put_line('There is no dead lock!'); end if; end;
no comment untill now