REM
/*
 * KillSessionS.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!
 */
Prompt
Prompt Generate ALTER SYSTEM DISCONNECT... script for all connections from a given machine/program
Prompt
Prompt The currently connected machines/users are:
Prompt

COLUMN prog FORMAT A256 WORD NEWLINE
COLUMN det  FORMAT A256 WORD
COLUMN kill FORMAT A132 WORD

set pages 0 feedback off ver off doc off head off lines 300 trimspool on
SELECT TO_CHAR(rownum,'0999')||' '||prog||', '||cnt||' session'||DECODE(cnt,1,'','s')||']' det
FROM	(SELECT ses.program||'-'||ses.terminal||'('||ses.machine||') ['||ses.username||'/'||ses.osuser prog
		,COUNT(*) cnt
	 FROM	v$session ses
	 WHERE	ses.username IS NOT NULL
	 AND	ses.audsid != USERENV('SESSIONID') -- ignore own session
	 AND	ses.status !='KILLED'
AND NOT EXISTS (SELECT 1 FROM v$process p WHERE p.addr=ses.paddr AND p.serial#=ses.serial# and p.background is NULL)
	 GROUP BY ses.program||'-'||ses.terminal||'('||ses.machine||') ['||ses.username||'/'||ses.osuser
	 ORDER BY 1) ilv
/

Prompt
ACCEPT pname PROMPT "Enter specific program/terminal/machine name to kill connections from: "
Prompt

SELECT	 'ALTER SYSTEM DISCONNECT SESSION '''||ses.sid||','||serial# ||''' IMMEDIATE;' kill
		,'/* ^'||ses.program||'-'||ses.terminal||'('||ses.machine||') '||ses.username||'/'||ses.osuser||' ^*/' prog
FROM	v$session ses
WHERE	ses.username IS NOT NULL
AND	ses.audsid != USERENV('SESSIONID') -- ignore own session
AND	ses.status !='KILLED'
AND	UPPER(ses.program||'-'||ses.terminal||'('||RTRIM(ses.machine)||') ['||ses.username||'/'||ses.osuser||']') LIKE UPPER('%&pname%')
AND NOT EXISTS (SELECT 1 FROM v$process p WHERE p.addr=ses.paddr AND p.serial#=ses.serial# and p.background is NULL)
ORDER BY 1

Spool KillSessionS.Tmp
/
Spool Off

Prompt
Prompt You now have a file called KillSessionS.Tmp to execute the above commands
Prompt

clear columns

Undef pname

/* SUMMARY - Summarise connected sessions by machine/program/user, prompt for a given machine/program/user and generate script to kill all matching sessions
 *
 * DETAIL - Killing a rougue session is relatively straight-forward as Oracle provides the 
 * ALTER SYSTEM 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. You may also need to kill off a number of sessions from a given machine, program or user in one go.
 *
 * This script summarises user sessions by machine/program/user - kill statements can then be generated for all sessions
 * for a given machine/program/user.
 *
 * Background processes and your own session are not included to remove the risk of picking one of them by mistake.

Sample output might look like:

SQL> start killsessions

Generate ALTER SYSTEM DISCONNECT... script for all connections from a given machine/program

The currently connected machines/users are:

0001 -(raclinux1.us.oracle.com) [OWB_OWNER/, 1 session]
0002 oracle@raclinux1.us.oracle.com (J003)-UNKNOWN (raclinux1.us.oracle.com) [OWB_OWNER/oracle, 1 session]
0003 sqlplus@raclinux1.us.oracle.com (TNS V1-V3)-pts/10(raclinux1.us.oracle.com) [SCOTT/oracle, 1 session]
0004 sqlplus@raclinux1.us.oracle.com (TNS V1-V3)-pts/8(raclinux1.us.oracle.com) [SCOTT/oracle, 1 session]

Enter specific program/terminal/machine name to kill connections from: scott

ALTER SYSTEM DISCONNECT SESSION '122,6334' IMMEDIATE;
/* ^sqlplus@raclinux1.us.oracle.com (TNS V1-V3)-pts/8(raclinux1.us.oracle.com) SCOTT/oracle ^*/
ALTER SYSTEM DISCONNECT SESSION '152,1816' IMMEDIATE;
/* ^sqlplus@raclinux1.us.oracle.com (TNS V1-V3)-pts/10(raclinux1.us.oracle.com) SCOTT/oracle ^*/

SQL> start KillSessionS.Tmp

System altered.

System altered.

 * (c) Tim Onions 2007
 * Script used at the database owners own risk.
 */
REM