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

1. Steven February 29, 2012 at 1:52 pm

   How to schedule the script from the linux or unix level? Thanks.

   LikeLike

   Reply ↓
   1. dmytro Post authorFebruary 29, 2012 at 3:08 pm

      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  
      

      LikeLike

      Reply ↓
2. Paavalan June 25, 2013 at 8:00 am

   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;

   LikeLike

   Reply ↓
3. Mohsen June 21, 2014 at 5:00 am

   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 …

   LikeLike

   Reply ↓