Kill Unwanted SESSIONS in ORACLE

Kill Sessions in Oracle

SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND';

OUTPUT:


   INST_ID        SID    SERIAL# SPID       USERNAME   PROGRAM
---------- ---------- ---------- ---------- ---------- ---------------------------------------------
         1         30         15 3859       TEST       sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
         1         23        287 3834       SYS        sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
         1         40        387 4663                  oracle@oel5-11gr2.localdomain (J000)
         1         38        125 4665                  oracle@oel5-11gr2.localdomain (J001)


 then kill these types

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

DISCONNECT SESSION is another technique to kill sessions

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;