Script to kill user sessions in Oracle

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
  1. 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
  2. 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 Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s