Do OracleConnections time out? If not, why are my Oracle sessions (in v$session) disappearing?

2
votes

I have a WinForms app which, when it first starts up, firstly creates an OracleConnection, and then calls a stored procedure that writes a record to a user_session table. The OracleConnection is a static member of a class that is deliberately never disposed, as we want the connection to exist for the lifetime of the app. The idea, essentially, is to keep track of who is using the app at any one time. When the app shuts down, it calls another stored procedure to delete the record from the user_session table.

This all works well enough, but we wanted to cater for the rare (?) occasion when the app crashes, and the record in our user_session table doesn't get cleared. To do this, we have another stored procedure that checks what sessions exist in the Oracle v$session view, and if it finds a session in user_session that doesn't exist in v$session, it cleans up the record in user_session. This also seems to work pretty well.

But now, we have a new WCF service, hosted in IIS 6. It also does the same start-up logic as the WinForms app - it creates the OracleConnection and doesn't dispose of it until the service shuts down.

Yet for some reason, this service's record in v$session is disappearing. This happens fairly randomly, but quite regularly (usually after a few hours of the service running). This causes our stored procedure to clear the service's session from our user_sessions table when it shouldn't, which I'm trying to fix.

So, on to my question: Do OracleConnections eventually time out, perhaps if they are idle for too long? If so, where is this time out configured? If not, why would my session get removed from v$session?

I had thought that if the OracleConnection is not disposed of, then the session (in v$session) would hang around indefinitely (or until the process is shut down/killed). And that seems to be how things work in the WinForms app. But this hasn't been our experience in the WCF service: as I've explained, the session just disappears, despite the OracleConnection still being open. (I've tried listening to the StateChange event, in case for some reason the OracleConnection was being closed, but the event never fires, so the connection is definitely still open. We've also configured IIS to not terminate idle processes, so it's not like the service is being shut down without our knowledge.)

Thanks in advance.

2
.netwcforaclesessionconnection
I doubt if I find it a good idea to keep connections open, anyway: are you sure the app domain for the webservice is not recycling. ASP.NET will recycle at least every 23 hours or earlier based on a bunch of things....That settings are separate from the idle Timeout configuration of process in IIS. – rene
I'm pretty sure that the app domain is not being recycled. The web service logs to a log file, and I can see from this log file when the service gets created etc. Nothing in the log file indicates that the service is restarting. – Justin

2 Answers

5
votes

It helps if you mention the oracle rdbms release that you use ....
Also, the network situation can have an effect. Are firewalls in use?

There are several reasons why an idle connection could get cleaned up:

  1. a firewall
  2. a user profile
  3. a resource manager idle timeout

2 and 3 are not enabled by default. If so, change it accordingly.

For 1, you first have to know if a firewall is in place. If so, you could enable Dead Client Detection. This should be done on the server and it regularly pings to the client to see if it is still present.

I hope this helps, Ronald

2
votes

Check your firewall, it may be killing sessions after a few hours. You can, alternatively set SQLNET.EXPIRE_TIME parameter. It may help in keeping the session open. Set it to a few minutes. I'm guessing .NET uses a NET8 client...