麻袋爸爸

扑捉数据库死锁

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;