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

4 thoughts on “Script to kill user sessions in Oracle”

    1. dmytro Post author

      You can schedule the script on the database level:

      CREATE OR REPLACE PROCEDURE a_test_procedure
AUTHID CURRENT_USER AS
BEGIN
  -- some code is here --
  NULL;
END;
/

-- Schedule the procedure to run at 3 AM every night
VARIABLE jobnumber NUMBER
BEGIN
  Dbms_Job.submit(
    :jobnumber,
    'a_test_procedure;',
    trunc(sysdate+1)+3/24, -- run at 3:00 AM
    'sysdate+1');           -- every night
  COMMIT;
END;
/

-- Removal of the job and the procedure:

SELECT job FROM dba_jobs WHERE what='a_test_procedure;';

-- use the job number from the above select
EXECUTE dbms_job.remove(124)

DROP PROCEDURE a_test_procedure

      Like

      Reply ↓
  2. Paavalan

    Brilliant script,
    Works perfectly
    Made a small modification to make this work on a RAC environment
    All you need is to change the inst_id and username like if you are specific about the instance and the username to be killed

    BEGIN
    FOR r IN (select username,inst_id, sid,serial# from gv$session where username like ‘%’ and status=’INACTIVE’ and inst_id=’1′)
    LOOP
    EXECUTE IMMEDIATE ‘alter system kill session ”’ || r.sid
    || ‘,’ || r.serial# || ””;
    END LOOP;
    END;

    Like

    Reply ↓
  3. Mohsen

    Hi

    This may be some how nooby But i can not understand what is all about these ‘ you inserted beside each other ???

    || ‘,’ || r.serial# || ””;

    there are 4 single quotes at the end … what does it mean and what’s the usage.
    i should add the script works as a charm and this is for my own interest …

    Like

    Reply ↓

Leave a comment