0

What is idle DB session? What factors we have to look into before terminating an idle DB session? When can we terminate it?

Puja
  • 1
  • 1
  • 4
  • 1
    there is really no need to terminate old sessions. PMON should go through and clean them up but if you absolutely must then make sure it has no pending transactions and the status is not active in v$session. – mmmmmpie Mar 08 '17 at 13:18
  • @mmmmmpie - don't be so sure. I've seen sessions running from an IDE which had seen no user activity *for weeks*. As far as PMON was concerned those sessions were live. – APC Mar 08 '17 at 13:27
  • what was their status in the DB? did you do any investigating as to why they could be showing active? – mmmmmpie Mar 08 '17 at 13:28
  • The IDE was still connected to the database so the session was live and the user process was valid. PMON only cleans up failed user processes. – APC Mar 08 '17 at 13:31
  • why was the IDE still connected to the DB? Sounds like an IDE problem not a DB problem. :) – mmmmmpie Mar 08 '17 at 13:43
  • Technically it was a user problem: people leaving their IDEs open after they had finished whatever they were doing. All I'm saying is, it's perfectly easy to have lots of open idle sessions, unless the DB owners take action. PMON won't tidy up those sessions. – APC Mar 08 '17 at 14:56

1 Answers1

0

An idle database session is one which is connected but the user hasn't done anything for a while. Such connected sessions still consume resource such as memory.

By default the permitted amount of idle time is unlimited:

select * from dba_profiles
where resource_time = 'IDLE_TIME';

The DBA can create a profile defining a set idle time limit. Users assigned that profile will be limited to that length of idleness. Sometime after that PMON will snipe the session: the session will still be connected but it will disconnect the next time the user tries to do something.

If your database does mot have a non-default IDLE_TIME then inactive sessions will remain for ever. This probably won't be a problem unless you have lots of them, and even then it's really a likely to be a user training issue (disconnect your IDE once you've finished working on whatever it is) or a bug in your application connection module.

To check whether you have too many long-term idle sessions you can run a query like this:

select program, osuser, user, logon_time, last_call_et, sid, serial#
from v$session
where status = 'INACTIVE'

The column of interest is last_call_et that's the number of seconds since the user last did anything in that session. If last_call_et is a big number (your mileage will vary) then you might want to consider culling that session. But really only bother if you need to free some resources.

APC
  • 144,005
  • 19
  • 170
  • 281