Tag Archives: loop

Script to kill user sessions in Oracle

4 Replies

Script to kill all sessions of a user in Oracle.

BEGIN
  FOR r IN (select sid,serial# from v$session where username = 'USER')
  LOOP
    EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid 
      || ',' || r.serial# || '''';
  END LOOP;
END;

Manual

Retrieve session identifiers and session serial number (which uniquely identifies a session’s objects):

select sid, serial# from v$session where username = 'USER'

The syntax to kill session is

alter system kill session 'sid,serial#'

Disconnecting session:

alter system disconnect session 'sid,serial#' post_transaction;
alter system disconnect session 'sid,serial#' immediate;

More information

Killing Oracle Sessions on www.oracle-base.com