How to kill Inactive sessions in ORACLE?

Hi, In this article we are going to learn how to kill inactive sessions. Sometimes there are so many inactive sessions available in the database. Due to these inactive sessions our database response a little slowly. So we need to kill them using the below steps.

Read: How to create a database link

Subscribe on YouTube

Step 1. Find total sessions details.

SQL> select status,count(*) from v$session group by status;

Step 2. Find sid & serial# for inactive sessions.

SQL> SELECT sid, serial#, status, username FROM v$session where status='INACTIVE';

Step 3. Kill session using below command.

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

If you have multiple sessions for killing, you must prepare the script.

Script for all sessions:

SQL> SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session;

The script only for Inactive sessions:

SQL> SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session 
     where status='INACTIVE';

The above queries will write the queries for you to kill Inactive sessions. It makes your task very easy.

Leave a Comment

%d bloggers like this:
Enable Notifications    OK No thanks