DB: Oracle 10g
O/S: Windows Server 2003 64 bits
I query list of Oracle sessions generated by web applications (filter by program=w3wp.exe)
select * from V$session
where UPPER(program) LIKE '%W3%'
order by logon_time
According to Logon_Time, why are still alive sessions from 31/07/2012 or 01/08/2012 or any session before today (21/08/2012)?
I have configured on sqlnet.ora:
SQLNET.EXPIRE_TIME= 20
So it means that every 20 min Oracle is checking if connections are still active.
All user-schemas have default profile. It would means that no session would never expired or die?
Added in response of Phil's comment:
Added in response of Phil's answer:
Best Answer
I suspect they're part of a connection pool & therefore haven't idled out because they're being used frequently.
INACTIVE
inv$session
merely means there isn't a SQL statement being executed at the exact moment you checkv$session
.If they're part of a connection pool they're doing their job properly by being logged in for long periods of time. The whole point of connection pooling is to remove the need for lots of logons/logoffs & keep persistent sessions for fast query startup - there's a much larger overhead logging in to execute one query, then disconnecting every time.
To get the last activity time for each session:
I'd advise against killing sessions unless you know that doing so will not cause problems on the application side (trying to use a session that's been killed, for example).
It may be the case that you're looking at an incorrectly configured connection pool which creates hundreds of connections once the app starts up - the connection pool may be an order of magnitude bigger than it needs to be. I suggest reaching out to the developers/application support staff & taking a look at how the connection pool is configured.
Having done a bit of research, w3wp.exe is the IIS Application Pool Process - you almost certainly want to talk to your IIS webserver admins to help get to the bottom of the connection pooling configuration.