REM
/*
 * KillSession.Sql (c) Tim Onions 2007
 * Please check this script works as expected on a test system before using on a production database.
 * The author can accept no liability or responsibility for any ill-effect it may have.
 * You are free to use this script on one condition - use it to improve the performance of your database!
 */

set verify off
col UNAM format a20  word  heading 'Oracle User/|OS User'
col RUNT format a08  word  heading 'Run Time'
col ltim format a20 word heading 'Logon Time'
--col etim format a20 word heading 'Connect Time'
col PROG format a40 word heading 'Program'
col SID  format a10 word heading 'SID/|Serial#'

set pages 60 feedback off

select SES.USERNAME||' / '||ses.osuser UNAM
      ,ses.sid||','||serial# sid
      ,ses.program||'-'||ses.terminal||'('||ses.machine||')' PROG
     ,ses.status "Status"
     , TO_CHAR(ses.logon_time,'DD MON YYYY HH24:MI:SS') ltim
     ,    ltrim(to_char(floor(SES.LAST_CALL_ET/3600),'09')) || ':'
       || ltrim(to_char(floor(mod(SES.LAST_CALL_ET,3600)/60), '09')) || ':'
       || ltrim(to_char(mod(SES.LAST_CALL_ET, 60),'09')) RUNT
from V$SESSION SES
where SES.USERNAME is not null
and SES.AUDSID!= userenv('SESSIONID') -- ignore own session
and sES.status!='KILLED'
order by 1
/

ACCEPT sid PROMPT 'Enter SID,SERIAL# (cut-and-paste from the above) of user to kill: '

SET Feedback On

Prompt
Prompt You can now issue (eg cut and paste it) either:
Prompt   alter system kill session '&sid';
Prompt or (from 8i)
Prompt   Alter system disconnect session '&sid' immediate;
Prompt
Prompt The latter is in theory better as "kill session" can leave a dead session.
Prompt

SET DOC off
/* SUMMARY - List all connected sessions, then prompt for sid,serial to kill a session
 *
 * DETAIL - Killing a rougue session is relatively straight-forward as Oracle provides the 
 * ALTER SYSTE DISCONNECT and KILL session commands. However, both of these require the session
 * to be killed to be identified by a combination of SID and SERIAL number.These are available
 * from the V$SESSION view but not something you want to get wrong! Killing the wrong application 
 * user is one thing, killing your own session is mightily embarassing but killing an essential background process will bring down the whole
 * database.
 *
 * This script lists sessions giving details of who they are and allows the SID and SERIAL number
 * to be cut-and-pasted and from this generates the syntax required to kill the session. That in turn
 * can be cut-and-pasted to ultimately get rid of the session - once you are sure you have the right one!

Sample output might look like:

SQL> start killsession

Oracle User/         SID/
OS User              Serial#    Program                                  Status   Logon Time           Run Time
-------------------- ---------- ---------------------------------------- -------- -------------------- --------
HR / oracle          144,2672   sqlplus@raclinux1.us.oracle.com (TNS     INACTIVE 25 MAY 2007 17:24:33 00:15:06
                                V1-V3)-pts/6(raclinux1.us.oracle.com)
OWB_OWNER /          120,2157   -(raclinux1.us.oracle.com)               ACTIVE   30 DEC 2006 15:47:34 00:00:03
OWB_OWNER / oracle   124,30     oracle@raclinux1.us.oracle.com           ACTIVE   30 DEC 2006 15:39:56 00:59:40
                                (J003)-UNKNOWN (raclinux1.us.oracle.com)                               
SCOTT / oracle       122,6319   sqlplus@raclinux1.us.oracle.com (TNS     INACTIVE 25 MAY 2007 17:27:40 00:11:57
                                V1-V3)-pts/8(raclinux1.us.oracle.com)
SCOTT / oracle       152,1690   sqlplus@raclinux1.us.oracle.com (TNS     INACTIVE 25 MAY 2007 17:28:13 00:11:24
                                V1-V3)-pts/10(raclinux1.us.oracle.com)

Enter SID,SERIAL# (cut-and-paste from the above) of user to kill: 144,2672 

You can now issue (eg cut and paste it) either:
.  ALTER SYSTEM KILL SESSION '144,2672 '
or (from 8i)
.  ALTER SYSTEM DISCONNECT SESSION '144,2672 ' immediate

The latter is in theory better as "kill session" can leave a dead session.

SQL> ALTER SYSTEM KILL SESSION '144,2672';

System altered.

 * (c) Tim Onions 2007
 * Script used at the database owners own risk.
 */
REM 
clear columns undef sid undef serial