Can we kill inactive sessions in Oracle?

The KILL SESSION command doesn't actually kill the session. It merely asks the session to kill itself. In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete. In these cases the session will have a status of "marked for kill". It will then be killed as soon as possible.

Table of Contents Show

How does Oracle handle INACTIVE sessions?
What does INACTIVE session mean in Oracle?
How do I kill a blocked session?
How do I kill multiple sessions?

Check the status to confirm:

SELECT sid, serial#, status, username FROM v$session;

You could also use IMMEDIATE clause:

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

The IMMEDIATE clause does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill. Have a look at Killing Oracle Sessions.

Update If you want to kill all the sessions, you could just prepare a small script.

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

Spool the above to a .sql file and execute it, or, copy paste the output and run it.

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.

Answer: Alll inactive sessions are zombies, and they don't have to be busy to be valid sessions. Perhaps these 300 people are all out to lunch, or at a meeting?

Use a v$session script to walk back and see. If you don't want to write your own, get the Oracle script download

If you KNOW that you want them to disconnect after becoming idle, nuke them with the "idle time" option. See killing inactive idle connections with the "idle" time and "connect" time options

If you think they are zombies you can kill then with "alter system kill session"

See killing Oracle sessions. You can also have a sniped sessions that ppear connected yet inactive and idle.

select
   s.status,
   count(1),
   s.username
from
   See code depot for full scripts
   v$process p,
   v$session s
where
   paddr(+)=addr
group by
   s.status,
   s.username
order by 1;

STATUS COUNT(1) USERNAME
-------- ---------- ------------------------------
ACTIVE 1 SYS
ACTIVE 16
INACTIVE 2 SYS
SNIPED 1 USR

And some you kill from the OS, using the PID:

http://www.dba-oracle.com/tips_killing_oracle_sessions.htm

1 - Gather session information from Oracle

2 - Kill the session at the OS-level

3 - Kill the session within Oracle using the "alter system kill session" command:

a) UNIX - I always locate the Server PID (SPID) from v$process and issue
the UNIX kill -9 command.

b The Windows command to kill this session would be as follows.
C:\oracle9i\bin>orakill ORCL92 768

How does Oracle handle INACTIVE sessions?

CREATE PROFILE old_session_profile LIMIT CONNECT_TIME 120 / -- Kill sessions that have been inactive for 1 hour. CREATE PROFILE inactive_session_profile LIMIT IDLE_TIME 60 / -- Kill sessions older than 2 hours or inactive for 1 hour.

What does INACTIVE session mean in Oracle?

It just means that someone is logged in but not executing SQL right at that instant. Your very own session is "INACTIVE" everytime you are in the process of typing in a query via sqlplus. It is a normal state to be in.

How do I kill a blocked session?

You can kill RMAN sessions which gives extra efor to the database like below. SELECT 'kill -9 ' || p. spid, s. username, 'alter system kill session ''' || SID || ',' || s.

How do I kill multiple sessions?

Update If you want to kill all the sessions, you could just prepare a small script. SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session; Spool the above to a . sql file and execute it, or, copy paste the output and run it.