Oracle批量杀死锁.tst

declare
  cursor mycur is
    select s.sid
          ,s.serial#
      from v$session   s
          ,v$lock      l
          ,dba_objects o
     where l.sid = s.sid
       and l.id1 = o.object_id(+)
       and s.TERMINAL <> 'ELLXI'--排除当前连接的PL/DEV用户(机器名)
       and S.USERNAME = 'USER'; --指定schema
  newcount        number;
  kill_sesion_sql varchar(2000);
begin

  for cur in mycur loop
 
    --    select count(1)
    --      into newcount
    --      from v$locked_object a
    --          ,v$session       b
    --     where a.session_id = b.sid
    --       and b.sid = cur.sid
    --       and b.serial# = cur.serial#;
    -- 
    --    if newcount >= 1 then
    kill_sesion_sql := 'alter system  kill session  ''' || cur.sid || ',' ||
                       cur.serial# || ''' ';
    dbms_output.put_line(kill_sesion_sql);
    execute immediate (kill_sesion_sql);
    --    end if;
 
  end loop;

end;