Oracle Kill/Terminate Session: Example

 Introduction:

When a SQL session is hanging for a long time, it make sense to terminate/kill the session. This is to improve developer time as well as to free up system resources in server side.

Here are some scenarios where KILL SESSION is useful:

1. System is in deadlock
2. While testing a query, developer missed a join, which makes internal query processing prohibitively expensive
3. Some sessions are taking up a lot of resources and important processes or users get very slow response

 

Steps to Kill Session:

Find the Problem Session:

Identify the session that needs to be killed. Easiest way is to get the user by Database Schema/User name. But it is possible that a single DB user may have many sessions open due to multiple reasons. In that case, finding session is tricky. Use any of the WHERE clauses in the below query to identify the session.

 

-- get session by User/Schema
SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME = 'BAI';
 
-- Get session by other parameters/inputs
SELECT SID || ',' || SERIAL# AS KILLSTR, SID, SERIAL#, X.*
FROM V$SESSION X
WHERE USERNAME = 'APPS'
AND MACHINE NOT LIKE 'XXABCWEB%' -- REMOVE WEB SERVER TO DB Server Connection
AND OSUSER NOT IN ('APPLPRD') -- REMOVE UNIX AND APPLN SERVER USERS
AND PROGRAM IN ('TOAD.EXE', 'SQL DEVELOPER', 'SQLPLUS.EXE') -- FILTER BY SQL Client Program
AND ROWNUM < 21;

 

Kill/Terminate the Problem Session:

Use inputs from above query in the KILL command

ALTER SYSTEM KILL SESSION '<SID>,<SERIAL>' IMMEDIATE;
 
ALTER SYSTEM KILL SESSION '1794,64680' IMMEDIATE
/
 

 

Keywords:

Kill Session, Terminate, Oracle, ALTER SESSION, PL/SQL, Query, SQL, SQLPLUS