ORACLE kill会话

ORACLE kill会话

1、查询会话

SELECT SID,SERIAL#,sql_id,MACHINE ,CLIENT_INFO FROM V$SESSION where SCHEMANAME='用户名'

2、kill 会话

alter system kill session 'sid,serial#' immediate;

备注：用户必须有权限，否则执行语句报权限不足

3、PL/SQL块批量kill会话

declare
  V_SQL VARCHAR2(1000);
begin
  FOR C IN (select b.SID s1, b.SERIAL# s2,b.sql_id
              from v$sqlarea A, V$SESSION B
             WHERE A.SQL_ID(+) = B.SQL_ID
               and b.CLIENT_INFO = '192.168.0.1'--会话使用对象IP
               AND b.sql_id is null
             order by disk_reads desc) LOOP
    begin
      V_SQL := 'alter system kill session ''' || C.S1 || ',' || C.S2 ||
               ''' immediate';
      EXECUTE IMMEDIATE V_SQL;
/*    exception
      when others then
        null;*/
    end;
  END LOOP;
end;