Oracle 中的空闲超时参数

IDLE timeout parameter in Oracle

提问by Irfan Zulfiqar

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 Oracleserver and any subsequent query or commit throws connection closed exception.

我们陷入了这样一种情况：我们的一个进程在不接触数据库的情况下需要 3 个小时的计算。在调用进程之前建立的连接被Oracle服务器关闭，任何后续查询或提交都会引发连接关闭异常。

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

在我们看来，问题与 Oracle 关闭由于某种原因闲置了那么长时间的连接有关。

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

我们尝试在 sqlnet.ora 中更改 EXPIRE_TIMEOUT 但这也无济于事。

What can we do to resolve this problem?

我们可以做些什么来解决这个问题？

采纳答案by Justin Cave

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.

默认情况下，Oracle 不会因不活动而关闭连接。您可以使用 IDLE_TIME 配置配置文件以导致 Oracle 关闭不活动的连接，但听起来您并没有这样做。您还可以将 Oracle 配置为检测死连接并在客户端没有响应时关闭连接——如果客户端被埋没了三个小时，它可能没有及时响应。但这似乎不太可能广告需要额外的配置步骤。

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.

您收到的实际 Oracle 错误消息将指出这些替代方案中的哪一个导致了您的问题。

回答by Rajesh Chamarthi

Irfan,

伊尔凡，

1. Please make sure you have the resource_limit=TRUE in the init.ora file for the changes to take effect.

2. Also, please check if the user you are trying to set the limit for is assigned to the default profile.

1. 请确保您在 init.ora 文件中有 resource_limit=TRUE 以使更改生效。

2. 此外，请检查您尝试为其设置限制的用户是否已分配给默认配置文件。

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'

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

从 dba_profiles 中选择配置文件、资源名称、限制，其中配置文件 ='PROFILE1' 和
资源名称 ='IDLE_TIME'

3 If the user is asigned to a custom profile make sure the parameters for the custom profile are set acordingly. You should also look at the connect_time parameter (in the default or the custom profile whichever applies to you. Once the connection time is exceeded, the connection is terminated . )

3 如果用户被分配到自定义配置文件，请确保相应地设置自定义配置文件的参数。您还应该查看 connect_time 参数（在适用于您的默认配置文件或自定义配置文件中。一旦超过连接时间，连接将终止。）

And finally, please note that if the current session started before the parameter was set, it will not be taken into effect. The changes kick-in only from the next session after you make 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

拉杰什

回答by Arpit Aggarwal

It seems actual reason for the connection closed exceptionis same as what @Justin Cavementioned in his answer:

其实际原因似乎connection closed exception与@Justin Cave在他的回答中提到的相同：

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.

根据我的经验，更有可能的情况是您的网络正在断开连接。例如，如果您通过防火墙进行连接，防火墙会经常关闭闲置时间过长的连接。

您收到的实际 Oracle 错误消息将指出这些替代方案中的哪一个导致了您的问题。

If still someone want to know the IDLE_TIMEand CONNECT_TIMEconfigured for a profile, then one can execute below query:

如果仍然有人想知道IDLE_TIME和CONNECT_TIME配置为配置文件，那么可以执行以下查询：

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

回答by Bob Jarvis - Reinstate Monica

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.

无论您使用什么数据库，假设您的连接在您想使用它时会处于活动状态都是一个坏主意。处理这个问题的一种方法是创建一个函数来返回到相关数据库的活动连接，并在每次需要给定数据库的句柄/对象/任何东西时调用它。该例程维护一个数据库列表及其关联的连接对象。如果在调用函数时连接对象处于活动状态，并且在函数对其执行某些操作以说服数据库保持句柄/对象/任何打开状态后返回对象，则一切正常。如果没有活动连接对象，例程会打开一个新对象并返回。有一个第二个例程在一个大约 1 分钟后到期的计时器上驻扎是很有用的。当计时器到期并调用第二个例程时，它会查看数据库连接列表，查找在设定时间内没有活动的连接（明显小于数据库的会话超时值）。那些长时间不活动的人会被关闭和清理。