oracle 超时后如何重新建立JDBC连接？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6152643/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to reestablish a JDBC connection after a timeout?
提问by Dmitry Chornyi
I have a long-running method which executes a large number of native SQL queries through the EntityManager (TopLink Essentials). Each query takes only milliseconds to run, but there are many thousands of them. This happens within a single EJB transaction. After 15 minutes, the database closes the connection which results in following error:
我有一个长期运行的方法，它通过 EntityManager (TopLink Essentials) 执行大量本机 SQL 查询。每个查询只需要几毫秒即可运行，但有成千上万的查询。这发生在单个 EJB 事务中。15 分钟后，数据库关闭连接，导致以下错误：
Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.1 (Build b02-p04 (04/12/2010))): oracle.toplink.essentials.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Closed Connection
Error Code: 17008
Call: select ...
Query: DataReadQuery()
at oracle.toplink.essentials.exceptions.DatabaseException.sqlException(DatabaseException.java:319)
.
.
.
RAR5031:System Exception.
javax.resource.ResourceException: This Managed Connection is not valid as the phyiscal connection is not usable
at com.sun.gjc.spi.ManagedConnection.checkIfValid(ManagedConnection.java:612)
In the JDBC connection pool I set is-connection-validation-required="true"and connection-validation-method="table"but this did not help .
在我设置的 JDBC 连接池中is-connection-validation-required="true"，connection-validation-method="table"但这没有帮助。
I assumed that JDBC connection validation is there to deal with precisely this kind of errors. I also looked at TopLink extensions (http://www.oracle.com/technetwork/middleware/ias/toplink-jpa-extensions-094393.html) for some kind of timeout settings but found nothing. There is also the TopLink session configuration file (http://download.oracle.com/docs/cd/B14099_19/web.1012/b15901/sessions003.htm) but I don't think there is anything useful there either.
我认为 JDBC 连接验证就是用来处理这种错误的。我还查看了 TopLink 扩展 (http://www.oracle.com/technetwork/middleware/ias/toplink-jpa-extensions-094393.html) 以了解某种超时设置，但一无所获。还有 TopLink 会话配置文件 (http://download.oracle.com/docs/cd/B14099_19/web.1012/b15901/sessions003.htm) 但我认为那里也没有任何有用的东西。
I don't have access to the Oracle DBA tables, but I think that Oracle closes connections after 15 minutes according to the setting in CONNECT_TIME profile variable.
我无权访问 Oracle DBA 表，但我认为 Oracle 根据 CONNECT_TIME 配置文件变量中的设置在 15 分钟后关闭连接。
Is there any other way to make TopLink or the JDBC pool to reestablish a closed connection?
有没有其他办法让TopLink或者JDBC池重新建立一个关闭的连接？
The database is Oracle 10g, application server is Sun Glassfish 2.1.1.
数据库为Oracle 10g，应用服务器为Sun Glassfish 2.1.1。
回答by Vineet Reynolds
All JPA implementations (running on a Java EE container) use a datasource with an associated connection pool to manage connectivity with the database.
所有 JPA 实现（在 Java EE 容器上运行）都使用具有关联连接池的数据源来管理与数据库的连接。
The persistence context itself is associated with the datasource via an appropriate entry in persistence.xml. If you wish to change the connection timeout settings on the client-side, then the associated connection pool must be re-configured.
持久性上下文本身通过 中的适当条目与数据源相关联persistence.xml。如果您希望更改客户端的连接超时设置，则必须重新配置关联的连接池。
In Glassfish, the timeout settings associated with the connection pool can be reconfigured by editing the pool settings, as listed in the following links:
在 Glassfish 中，可以通过编辑池设置来重新配置与连接池关联的超时设置，如以下链接中所列：
On the server-side (whose settings if lower than the client settings, would be more important), the Oracle database can be configured to have database profiles associated with user accounts. The session idle_time and connect_time parameters of a profilewould constitute the timeout settings of importance in this aspect of the client-server interaction. If no profile has been set, then by default, the timeout is unlimited.
在服务器端（如果其设置低于客户端设置，则更重要），可以将 Oracle 数据库配置为具有与用户帐户关联的数据库配置文件。配置文件的session idle_time 和 connect_time 参数将构成客户端-服务器交互这方面的重要超时设置。如果没有设置配置文件，则默认情况下，超时是无限的。
回答by Gary Myers
Unless you've got some sort of RAC failover, when the connection is terminated, it will end the session and transaction.
除非您有某种 RAC 故障转移，否则当连接终止时，它将结束会话和事务。
The admins may have set into some limits to prevent runaway transactions or a single job 'hogging' a connection in a pool. You generally don't want to lock a connection in a pool for an extended period.
管理员可能已经设置了一些限制，以防止失控的事务或单个作业“占用”池中的连接。您通常不希望长时间锁定池中的连接。
If these queries aren't necessarily part of the same transaction, then you could try terminating and restarting a new connection.
如果这些查询不一定是同一事务的一部分，那么您可以尝试终止并重新启动新连接。
Are you able to restructure your code so that it completes in under 15 minutes. A stored procedure in the background may be able to do the job a lot quicker than dragging the results of thousands of operations over the network.
您是否能够重组您的代码，使其在 15 分钟内完成。与通过网络拖动数千个操作的结果相比，后台的存储过程可能能够更快地完成这项工作。
回答by JoshDM
I see you set your connection-validation-method="table"and is-connection-validation-required="true", but you do not mention that you specified the table you were validating on; did you set validation-table-name="any_table_you_know_exists"and provide any existing table-name? validation-table-name="existing_table_name"is required.
我看到你设置了你的connection-validation-method="table"and is-connection-validation-required="true"，但你没有提到你指定了你正在验证的表；您是否设置validation-table-name="any_table_you_know_exists"并提供了任何现有的表名？ validation-table-name="existing_table_name"是必须的。
See this article for more details on connection validation.
Related StackOverflow article with similar problem- he wants to flush the entire invalid connection pool.
具有类似问题的相关 StackOverflow 文章- 他想刷新整个无效连接池。
