IDLE timeout parameter in Oracle

Question

We are stuck in a situation where one of our processes is taking 3 hours of computing without touching the database. The connection that was taken before calling the process gets closed by the Oracle server and any subsequent query or commit throws connection closed exception.

It appears to us that the problem is related to Oracle closing the connection that is idle for that long for some reason.

We tried changing EXPIRE_TIMEOUT in sqlnet.ora but that didn't help either.

What can we do to resolve this problem?

Answer 1

No matter what database you're using it's a bad idea to assume your connection is going to be live when you want to use it. One way to handle this is to create a function to return an active connection to the database in question, and to call it every time you need a handle/object/whatever for a given database. The routine maintains a list of databases and their associated connection object. If the connection object is live when the function is called all's well and good and the object is returned after the function does something with it to convince the database to keep the handle/object/whatever open. If there's no live connection object the routine opens a new one and returns that. It's useful to have a second routine that camps out on a timer that expires after 1 minute or so. When the timer expires and the second routine is called it looks through the list of database connections, looking for ones with no activity for a set amount of time (something significantly less that the database's session timeout value). Those that have been inactive for too long get closed and cleaned up.

Answer 2

It seems actual reason for the connection closed exception is same as what @Justin Cave mentioned in his answer:

The more likely situation in my experience is that your network is dropping the connection. If you are connecting via a firewall, for example, the firewall will frequently close connections that have been idle too long.

The actual Oracle error message you are receiving will indicate which of these alternatives is causing your problem.

If still someone want to know the IDLE_TIME and CONNECT_TIME configured for a profile, then one can execute below query:

select * from user_resource_limits user_resource where user_resource.resource_name in ('IDLE_TIME','CONNECT_TIME');