IDLE wait parameter in Oracle

Question

We are stuck in a situation where one of our processes takes 3 hours of computing without touching the database. The connection that was made before the process call is closed by the Oracle server, and any subsequent request or commit causes a closed connection exception.

It seems to us that the problem is that Oracle closes the connection, which for some reason is idle for some reason.

We tried to change EXPIRE_TIMEOUT in sqlnet.ora, but that didn't help either.

What can we do to solve this problem?

Answer 1

What is the error that occurs when trying to use a connection?

Oracle by default will not close the connection due to inactivity. You can customize the profile with IDLE_TIME to force Oracle to close inactive connections, but this does not look like you did it. You can also configure Oracle to detect dead connections and close the connection if the client does not answer - if the client is buried for three hours, it is possible that he does not respond in a timely manner. But ads are less likely to require additional customization steps.

A more likely situation in my experience is that your network is disconnecting. For example, if you connect through a firewall, the firewall often closes connection downtimes that are too long.

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

Answer 2

Irfan

• Please make sure that init.ora has limit_resource = TRUE for the changes to take effect.

• Also, check if the user to whom you are trying to set a limit is assigned to the default profile.

select profile from dba_users where username = 'TEST_USER'; PROFILE1 

select profile, resource_name, limit from dba_profiles, where profile = 'PROFILE1' and
resource_name = 'IDLE_TIME'

3 If the user is assigned to an arbitrary profile, make sure that the settings for the custom profile are set accordingly. You should also look at the connect_time parameter (the default or a custom profile that suits you. After the connection time expires, the connection is terminated.)

And finally, note that if the current session started before the parameter was set, it will not be put into effect. Changes come only after the next session after making the changes.

Useful links.

 http://www.adp-gmbh.ch/blog/2005/april/17.html http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:453256655431 

Thanks,

Rajesh

Answer 3

No matter which database you use, it is a bad idea to assume that your connection will live when you want to use it. One way to handle this is to create a function to return the active connection to the appropriate database and call it every time you need a handle / object / everything for a given database. The program maintains a list of databases and an associated connection object. If the connection object is alive when the function is called all is well and good, and the object is returned after the function does something with it to convince the database to keep the handle / object / open. If there is no live connection object, the procedure opens a new one and returns this. It is helpful to have a second routine that runs on a timer that expires in 1 minute or so. When the timer expires and the second subroutine is called, it scans the list of database connections, looking for those that have not been active for a set period of time (which is much less than the value of the database session timeout). Those that are inactive for too long will close and clear.

Answer 4

It seems that the actual reason for the connection closed exception is the same as in @Justin Cave :

A more likely situation in my experience is that your network is downgrading. If you connect through a firewall, for example, the firewall often closes connections that have been idle for too long.

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

If someone wants to know the IDLE_TIME and CONNECT_TIME configured for the profile, you can run below query:

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