User Sessions

 Finding and Killing user session 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';

   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)



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

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