Killing Oracle Sessions

detail http://www.oracle-base.com/articles/misc/KillingOracleSessions.php

Killing sessions can be very destructive if you kill the wrong session, so be very careful when identifying the session to be killed. If you kill a session belonging to a background process you will cause an instance crash.

Identify the offending session using the V$SESSION or GV$SESSION view as follows.

Identify the offending session using the V$SESSION or GV$SESSION view as follows.

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>

The SID and SERIAL# values of the relevant session can then be substituted into the commands in the following sections.

ALTER SYSTEM KILL SESSION

The basic syntax for killing a session is shown below.

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