Steps - Kill Oracle or SQL Session - sqlplus

You are connected to TOAD. You ran a complex query and the window is hanging for a long time. Even the CANCEL button does not respond appropriately. What to do?

A very common problem, Oracle Developers have. Some quick solutions are <CNTL> C and <CNTL> D buttons. But many times, they also fail.

Here is a more complete and perfect way to kill the session.


Step 1: Identify the session - SID and Serial

Use appropriate WHERE clauses to identify the hanging session. You may use client (Toad/SQL - MODULE), OSUSER (Operating System User, Eg: CORP/AbThomas), MACHINE (Eg: server_dns_name) or TIMESTAMPS.


-- get non-unix sql sessions, kill one session
SELECT S.OSUSER, SUBSTR(S.SID || ',' || S.SERIAL#, 0, 20) AS KILL_STRING, MODULE, MACHINE, LOGON_TIME
FROM   V$SESSION S
where OSUSER = 'athomas'
-- AND MODULE LIKE 'T%' -- 'SQ%'
-- AND MACHINE LIKE '%NRBVUEBSAS02%'
-- AND MODULE LIKE 'JDBC Thin Client%'

In the results from above query, the module SQL is SQL Plus client, TOAD is Toad Client and anything related to Java or JVM will be the connections created from web server. (The Application Server connects to Database server through JDBC conneciton).

Step 2: Open another window, close the session:
Identify the Session Id and Serial (Second column in the above query) and substitute in the below line ...

ALTER SYSTEM KILL SESSION '876,24615'

The connection will be lost  with a message. Still you have the SQLs in the screen. In this way, all your query will be preserved, even though the connection is lost.


Simple steps. But very useful many times ... NJoy :)