Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Raj.

Asked: October 16, 2000 - 12:19 am UTC

Answered by: Tom Kyte - Last updated: May 12, 2020 - 8:21 am UTC

Category: Database - Version: 8.0.6

Viewed 100K+ times! This question is

You Asked

Hi Tom,


Whenever I try to check the sessios logged on to my system
we find say 100 users loigged on out of which hardly 20 sessions are Active.

Should we have some way to kill the inactive sessions.

Pls tell me how to handle this condition

Thanks

Raj

and we said...

Well, you can use

alter system kill session 'sid,serial#'

where sid and serial are retrieved from v$session -- however -- why would you want to arbitrarily kill inactive sessions? 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.

and you rated our response

  (13 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Inactive Sessions

December 05, 2005 - 7:19 am UTC

Reviewer: A reader

Tom,

This means inactive sessions can become active when user executes some sql.

Yes/ No

Tom Kyte

Followup  

December 06, 2005 - 4:15 am UTC

pretty much, unless a session is executing some sql, it'll be "inactive" - not active.

Inactive sessions

December 05, 2005 - 7:31 am UTC

Reviewer: A reader from UK

Tom,

This means inactive sessions can become active when user executes some sql.

Yes/ No ?

Session Cleanup

February 09, 2006 - 6:28 am UTC

Reviewer: Dave Martin

Tom,
assuming you have no Dead Connection Detection configured and no idle time set, will your inactive/dead sessions stay in the system for ever, assuming you dont kill them or bounce the db?

I have a db that was bounced on 10/1/2006 and the earlier (non-system) logon time in v$session is 27/1/2006.

I would expect to see older dead or inactive sessions than that, knowing my users, I expect to see more sessions lying around as a result of broken web connections, PCs rebooted etc..?

Tom Kyte

Followup  

February 10, 2006 - 10:58 am UTC

for inactive - sure, inactive sessions are just that - inactive. not evil, not bad, not good, not anything. Just INACTIVE. Nothing wrong or bad about inactive (unless you personally consider it otherwise, then you set timeouts).



good thing you gave me two dates :) Else, I would have thought 10/1/2006 was in the future.....

Guess your users are better than you thought they were.

thanks..

February 13, 2006 - 4:19 am UTC

Reviewer: Dave Martin from Netherlands

guess they must be.

Thanks,

Dave

13/2(FEB:))/2005

May 24, 2010 - 2:46 pm UTC

Reviewer: Alexander

So is it false to say a session can be running sql but also show up as inactive? I swear you explained to me once that this can be true. If for example you have a query running for hours, it would show inactive, and the last_call_et column would represent the number of seconds since the query started and not how long it's (the session) been idle.
Tom Kyte

Followup  

May 24, 2010 - 3:12 pm UTC

I can

a) open sql - that'll be active
b) let the client do something, like ask the end user "would you like to see the results?" - that'll be inactive, but I'm still running my sql
c) fetch 25 rows - that'll be active
d) wait for the user to hit page down - that'll be inactive
e) fetch 25 more rows - that'll be active
f) wait for the user....

and so on. it might takes "days" to complete the query and you'll be going active/inactive over and over.


The last call et column would be reset every time the status flipped from active/inactive - it would not represent the number of seconds since the query started - it would be the number of seconds the session was in the current state (active or not).

April 14, 2011 - 11:50 pm UTC

Reviewer: Paul

I was discussing with topic with one of my senior DBA and his response was "We need to clean-up the inactive sessions especially in RAC database for better performance" Is this true?
Tom Kyte

Followup  

April 15, 2011 - 9:57 am UTC

If you have lots of inactive session - so many that they are consuming gobs of RAM - and you feel the need to clean them up

You have a problem that should be solved once and for all. Get to the root cause, what is CAUSING these inactive sessions that you can just kill? It sounds like you have a bug in your middle tier application and it is losing connections in a connection pool - that is some developer has:

a) grabbed a connection
b) hit and exception
c) propagated over and out of the scope of the connection handle from (a)
d) so they can never give it back - they lost it

that would be the only type of session it would be safe to kill.

And you shouldn't kill it, you should fix it - the application has a pretty bad bug.

Awesome as usual

April 16, 2011 - 10:16 pm UTC

Reviewer: Paul

Thanks for your quick response. Your explanations are awesome!!

April 09, 2014 - 1:41 pm UTC

Reviewer: usha from INDIA

Hi Tom,

I have a similar question,Can anybody help me out?

when a session can becomes inactive permanently,

I got an issue like number of processes exceeded interms says that number of sessions exceeded.

Will the database cleans the sessions those have been inactive for long time ?

Regards
usha
Tom Kyte

Followup  

April 16, 2014 - 4:49 pm UTC

no, why would it?

you can set a profile to disconnect idle sessions - but YOU DO NOT WANT TO, it'll break your connection pool.

If these session exist, but your application is not able to use them - then you have a bug in your code, you are using a connection pool. your code grabs a connection from the pool. You hit an exception somewhere and due to bad error handling, you forget to return the connection to the pool. You are leaking connections (probability of this paragraph being true so close to 100% that we can say "it is 100%)

just wrote that yesterday:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:8178218400346699475


Bit confusing

April 28, 2014 - 12:42 pm UTC

Reviewer: A

Hello Tom,
Bit confused and I have question on what you said earlier

a) grabbed a connection
b) hit and exception
c) propagated over and out of the scope of the connection handle from (a)
d) so they can never give it back - they lost it

Will be session be inactive state for ever if application user closes his session from the front-end or there was an exception (from db side) and the user closed the application?

Thanks
A

Find root cause for inactive session

March 16, 2017 - 1:36 pm UTC

Reviewer: Mansi Raval from India

I have similer issue. Inactive sessions are increasing from application server. I am not able to find root cause for this.
It keeps increasing and does not allow user to use application.
this lead us to bounce application server.

I can see one query returning around 20 million rows and more than 6 sessions are there for such query.
I am seeing this in gv$SQL

Is such queries increased inactive session. because when I search for inactive session,small queries returning less no. of rows or small IO also gone into inactive session.
Connor McDonald

Followup  

March 19, 2017 - 5:22 am UTC

As Tom said a couple of reviews up:


"You can set a profile to disconnect idle sessions - but YOU DO NOT WANT TO, it'll break your connection pool.

If these session exist, but your application is not able to use them - then you have a bug in your code, you are using a connection pool. your code grabs a connection from the pool. You hit an exception somewhere and due to bad error handling, you forget to return the connection to the pool. You are leaking connections (probability of this paragraph being true so close to 100% that we can say "it is 100%) "

If you *cant* find the cause of the leak, then you *might* have to use a profile to clean them up, but the reality is - it is the application layer that needs to manage connections.

Application session and database status

March 20, 2017 - 9:30 am UTC

Reviewer: Pol

When I try to Insert 10 Rows in Oracle database.
So when I insert 3 rows using vb.net and i check session status from back-end side using select * from v$session.
My Question is here i get session status is "INACTIVE".
but still i am connect since 10 seconds.
so why i get status is "INACTIVE"?
Connor McDonald

Followup  

March 22, 2017 - 1:40 am UTC

The moment you have finished inserting a row into the database, your session will be inactive (because its "done").

If you have 10 inserts (1 at a time), then the session will be active for a tiny fraction whilst each insert occurs, and then be inactive.

ACTIVE means I am *currently* running something. So unless it is a long running operation (eg more than a couple of seconds), the session will generally appear an inactive.

Inactive Sessions

April 02, 2019 - 1:13 pm UTC

Reviewer: sairaj from india

Hi Tom ,

Any JVM is there to clear the inactive session after running the batch processors ?that could create a solution for us .after running the batch processors we are seeing many inactive sessions that leading to the server down .
Chris Saxon

Followup  

April 03, 2019 - 10:08 am UTC

That will depend on the specifics of the application server you're using. This is outside our arena - you'll need to find someone who knows about your environment.

we are seeing many inactive sessions that leading to the server down

Inactive sessions are just that - inactive. They're not doing anything. I'm not sure how this leads to "server down"... unless you're hitting the process limit?

INACTIVE session

May 11, 2020 - 10:34 am UTC

Reviewer: Dinesh kanna

Thanks for this thread.

I have few doubts. Recently I have joined an org, In which

We do have alert monitoring system where if there are more than 250 connections(Inactive) send an alert.

1. Do we really need to monitor this? Since Inactive aren't going to create any harm( as per this thread info. Correct me if I am wrong)
2. What is the idle thread time out parameter plays a role with respect to INACTIVE/ACTIVE connections?
3. Assume the same oracle instance has been shared with multiple users for testing. In this case is that more than 250 INCATIVE connections will create an impact?

Please clarify.
Connor McDonald

Followup  

May 12, 2020 - 8:21 am UTC

1) Maybe.

If those inactive session are holding locks, it might be an issue. If you are *also* monitoring for sessions that get block on locks, then it is less of an issue. Also depends on how close you are to the instance 'sessions' limit.

2) When you pass the idle timeout, the sessions are become "sniped". They'll be cleaned up in due course by a background process.

3) See (1)