To kill The Oracle Sessions

To kill The Oracle Sessions

1)Check the session to be killed

#killing session related to background process may lead to Instance crash

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>

2)Kill the session

ALTER SYSTEM KILL SESSION 'sid,serial#' immediate;

#In case of RAC setup we have to specify Instance/Node number.

ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_ID' immediate;

3)Kill session from OS prompt

#This should be  the last option to kill the session.

kill -9 SPID