IDLE timeout parameter in Oracle

oracle connection python-idle
Irfan Zulfiqar picture Irfan Zulfiqar · Dec 27, 2009 · Viewed 84.9k times · Source

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

Justin Cave picture Justin Cave · Dec 28, 2009

What is the error you get when you try to use the connection?

Oracle by default will not close a connection due to inactivity. You can configure a profile with an IDLE_TIME to cause Oracle to close inactive connections, but it doesn't sound like you've done that. You can also configure Oracle to detect dead connections and close the connection if the client doesn't respond-- if the client is buried for three hours, it's possible that it's not responding in a timely fashion. But that seems less likely ad requires additional configuration steps.

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.

Related questions

Oracle - What TNS Names file am I using?

Sometimes I get Oracle connection problems because I can't figure out which tnsnames.ora file my database client is using. What's the best way to figure this out? ++happy for various platform solutions.

Read More
Relation between Oracle session and connection pool

Let me explain the set up first. We have an oracle server running on a 2GB RAM machine. The Db instance has the init parameter "sessions" set to 160. We have the application deployed on Websphere 6.1. The connection pool settings is …

Read More
java.sql.SQLException: Io exception: Broken pipe how to recover without restart?

In my application I use connection to Oracle, when connection lost and I try to re-connect I receive exception: java.sql.SQLException: Io exception: Broken pipe at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:124) at oracle.jdbc.driver.DatabaseError.throwSqlException(…

Read More