At one site I can connect to the Oracle Database with SQL Developer, leave it idle for a long time (e.g., > 60 minutes), and return, and it's fine. At a second site, if it stays idle for more than 5-10 minutes (I haven't counted exactly), it leaves SQL Developer in a state where new operations will timeout and I need to manually "Disconnect" and then reconnect in order to do anything useful. This seems to be a connection timeout at the second site, and I don't know what causes it (and I'd like to know how to turn it off, although this is not my main question).
My program uses ODP.NET and processes data that comes in spurts. Every 30 minutes (for the sake of discussion) it will get a bunch of data to process which will involve a number of repeated connections. It also uses Connection Pooling. I've set the Connection Pool to use a Lifetime of 5 minutes.
What I'm seeing at the second site (and not at the first) is my program will get connection timeout exceptions (e.g., ORA-03113) at the beginning of each spurt of data. What I believe is happening is that during the spurt of data, the connection pool is used as designed. At the end of the spurt the "Connection Lifetime" is checked, and the connection is not too old, so it is left in the connection pool. Then, 30 minutes later when new data arrives, the connection is taken out of the pool (and not checked for a lifetime or timeout) and used, and is timeing out, just as I see in SQL Developer.
How can I avoid the connection timeout but still take advantage of the Connection Pool during the spurts? It seems from the documentation (and my experience) that the connection is only checked for Lifetime when it goes into the pool, and not when it comes out.
This is a really old question but I've been experiencing some similar issues with an application and so I think some of the information might help anyone else who trips across this question.
The TL;DR summary is that ODP.NET drivers and the .NET implementation don't play nicely with each other and so your normal run of the mill connection pooling settings don't seem to work exactly how you would expect.
IDLE_TIME
set to a value in your Oracle user profile (and not UNLIMITED
) then eventually these long running idle parameters will be SNIPED
by the database. This can end up causing problems on the .NET side because unless you are explicitly checking that your connections are still open, .NET is going to serve up these SNIPED
connections as if they are still available (thus throwing the above timeout ORA error). Data Validation=True;
in your connection string. This ensures that .NET will check for session connectivity before it serves the connection up to the next service call. When this validation sees a SNIPED
session it removes it from the .NET connection pool.Given this information, it is most likely that the OP's original problem was only appearing in the one site from a combination of different database settings and/or the frequency of the .NET calls to the database. He might have had the problem in both environments but if users in one environment were making calls frequently enough for Connection Lifetime
to do it's job then he would never see these timeouts in that database.
Now I still haven't figured out how to kill an idle connection in .NET before any Oracle IDLE_TIME sniping takes place but as long as you use that Data Validation = True
parameter you should hopefully be able to work around this problem.