oracle ORA-01000: 使用 Spring SimpleJDBCCall 时超出了最大打开游标数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1436436/
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
ORA-01000: maximum open cursors exceededwhen using Spring SimpleJDBCCall
提问by Brendan Heywood
We are using Spring SimpleJdbcCall to call stored procedures in Oracle that return cursors. It looks like SimpleJdbcCall isn't closing the cursors and after a while the max open cursors is exceeded.
我们使用 Spring SimpleJdbcCall 来调用 Oracle 中返回游标的存储过程。看起来 SimpleJdbcCall 没有关闭游标，一段时间后超过了最大打开游标。
ORA-01000: maximum open cursors exceeded ; nested exception is java.sql.SQLException: ORA-01000: maximum open cursors exceeded spring
There are a few other people on forums who've experienced this but seemingly no answers. It looks like me as a bug in the spring/oracle support.
论坛上还有其他一些人经历过这种情况，但似乎没有答案。看起来我是 spring/oracle 支持中的一个错误。
This bug is critical and could impact our future use of Spring JDBC.
这个错误很关键，可能会影响我们未来对 Spring JDBC 的使用。
Has anybody come across a fix - either tracking the problem to the Spring code or found a workaround that avoids the problem?
有没有人遇到过修复 - 要么将问题跟踪到 Spring 代码，要么找到避免该问题的解决方法？
We are using Spring 2.5.6.
我们使用的是 Spring 2.5.6。
Here is the new version of the code using SimpleJdbcCall which appears to not be correctly closing the result set that the proc returns via a cursor:
这是使用 SimpleJdbcCall 的新版本代码，它似乎没有正确关闭 proc 通过游标返回的结果集：
...
SimpleJdbcCall call = new SimpleJdbcCall(dataSource);
Map params = new HashMap();
params.put("remote_user", session.getAttribute("cas_username") );
Map result = call
.withSchemaName("urs")
.withCatalogName("ursWeb")
.withProcedureName("get_roles")
.returningResultSet("rolesCur", new au.edu.une.common.util.ParameterizedMapRowMapper() )
.execute(params);
List roles = (List)result.get("rolesCur")
The older version of the code which doesn't use Spring JDBC doesn't have this problem:
不使用Spring JDBC的旧版本代码没有这个问题：
oracleConnection = dataSource.getConnection();
callable = oracleConnection.prepareCall(
"{ call urs.ursweb.get_roles(?, ?) }" );
callable.setString(1, (String)session.getAttribute("cas_username"));
callable.registerOutParameter (2, oracle.jdbc.OracleTypes.CURSOR);
callable.execute();
ResultSet rset = (ResultSet)callable.getObject(2);
... do stuff with the result set
if (rset != null) rset.close(); // Explicitly close the resultset
if (callable != null) callable.close(); //Close the callable
if (oracleConnection != null) oracleConnection.close(); //Close the connection
It would appear that Spring JDBC is NOT calling rset.close(). If I comment out that line in the old code then after load testing we get the same database exception.
看起来 Spring JDBC 没有调用 rset.close()。如果我注释掉旧代码中的那一行，那么在负载测试之后我们会得到相同的数据库异常。
采纳答案by Brendan Heywood
After much testing we have fixed this problem. It is a combination of how we were using the spring framework and the oracle client and the oracle DB. We were creating new SimpleJDBCCalls which were using the oracle JDBC client's metadata calls which were returned as cursors which were not being closed and cleaned up. I consider this a bug in the Spring JDBC framework in how it calls metadata but then does not close the cursor. Spring should copy the meta data out of the cursor and close it properly. I haven't bothered opening an jira issue with spring because if you use best practice the bug isn't exhibited.
经过多次测试，我们已经解决了这个问题。它结合了我们使用 spring 框架、oracle 客户端和 oracle DB 的方式。我们正在创建新的 SimpleJDBCCalls，它使用 oracle JDBC 客户端的元数据调用，这些调用作为未关闭和清理的游标返回。我认为这是 Spring JDBC 框架中调用元数据但不关闭游标的错误。Spring 应该从游标中复制元数据并正确关闭它。我没有费心打开 spring 的 jira 问题，因为如果您使用最佳实践，则不会显示错误。
Tweaking OPEN_CURSORS or any of the other parameters is the wrong way to fix this problem and just delays it from appearing.
调整 OPEN_CURSORS 或任何其他参数是解决此问题的错误方法，只会延迟它的出现。
We worked around it/fixed it by moving the SimpleJDBCCall into a singleton DAO so there is only one cursor open for each oracle proc that we call. These cursors are open for the lifetime of the app - which I consider a bug. As long as OPEN_CURSORS is larger than the number of SimpleJDBCCall objects then there won't be hassles.
我们通过将 SimpleJDBCCall 移动到单例 DAO 来解决/修复它，因此我们调用的每个 oracle proc 只有一个打开的游标。这些游标在应用程序的整个生命周期内都是打开的——我认为这是一个错误。只要 OPEN_CURSORS 大于 SimpleJDBCCall 对象的数量，就不会有麻烦。
回答by andrej
Well, I've got this problem when I was reading BLOBs. Main cause was that I was also updating table and the Statement containing update clause was not closed automatically. Nasty cursorleak eats all free cursors. After explicit call of statement.close() the error disappears.
好吧，我在阅读 BLOB 时遇到了这个问题。主要原因是我也在更新表，并且包含更新子句的语句没有自动关闭。讨厌的游标泄漏会吃掉所有空闲游标。显式调用 statement.close() 后，错误消失。
Moral - always close everything, don't rely on automatic close after disposing Statement.
道德 -始终关闭所有内容，不要依赖处理 Statement 后的自动关闭。
回答by azp74
Just be careful setting OPEN_CURSORS to higher and higher values as there are overheads and it could just be band-aiding over an actual problem/error in your code.
请小心将 OPEN_CURSORS 设置为越来越高的值，因为存在开销，并且它可能只是对代码中的实际问题/错误提供帮助。
I don't have experience with the Spring side of this but worked on an app where we had many issues with ORA-01000 errors and constantly adjusting OPEN_CURSORS just made the problem go away for a little while ...
我没有这方面的 Spring 方面的经验，但在一个应用程序上工作，在该应用程序中，我们遇到了许多 ORA-01000 错误问题，并且不断调整 OPEN_CURSORS 只是让问题消失了一段时间......
回答by duffymo
I can promise you that it's not Spring. I worked on a Spring 1.x app that went live in 2005 and hasn't leaked a connection since. (WebLogic 9., JDK 5). You aren't closing your resources properly.
我可以向你保证，这不是春天。我开发了一个 Spring 1.x 应用程序，该应用程序于 2005 年上线，此后一直没有泄漏连接。（WebLogic 9.，JDK 5）。您没有正确关闭资源。
Are you using a connection pool? Which app server are you deploying to? Which version of Spring? Oracle? Java? Details, please.
你在使用连接池吗？您要部署到哪个应用服务器？哪个版本的Spring？甲骨文？爪哇？详情，请。
回答by duffymo
Oracle OPEN_CURSORS is the key alright. We have a small 24x7 app running against Oracle XE with only a few apparently open cursors. We had intermittent max open cursors errors until we set the OPEN_CURSORS initialization value to > 300
Oracle OPEN_CURSORS 是关键。我们有一个针对 Oracle XE 运行的 24x7 小应用程序，其中只有几个明显打开的游标。我们有间歇性的最大打开游标错误，直到我们将 OPEN_CURSORS 初始化值设置为 > 300
回答by kdgregory
The solution is not in Spring, but in Oracle: you need to set the OPEN_CURSORSinitialization parameter to some value higher than the default 50.
解决方案不是在 Spring 中，而是在 Oracle 中：您需要将OPEN_CURSORS初始化参数设置为高于默认 50 的某个值。
Oracle -- at least as-of 8i, perhaps it's changed -- would reparse JDBC PreparedStatement objects unless you left them open. This was expensive, and most people end up maintaining a fixed pool of open statements that are resubmitted.
Oracle —— 至少从 8i 开始，也许它已经改变了 —— 会重新解析 JDBC PreparedStatement 对象，除非你让它们保持打开状态。这是昂贵的，大多数人最终会维护一个固定的重新提交的开放语句池。
(taking a quick look at the 10i docs, they explicitly note that the OCI driver will cache PreparedStatements, so I'm assuming that the native driver still recreates them each time)
（快速浏览 10i 文档，他们明确指出 OCI 驱动程序将缓存 PreparedStatements，所以我假设本机驱动程序每次仍然重新创建它们）
