oracle java代码中的最大打开游标超出异常
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39716391/
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
maximum open cursors exceeded exception in java code
提问by Shrikant Dande
this my code to execute update query
这是我执行更新查询的代码
public boolean executeQuery(Connection con,String query) throws SQLException
{
boolean flag=false;
try
{
Statement st = con.createStatement();
flag=st.execute(query);
st.close();
st=null;
flag=true;
}
catch (Exception e)
{
flag=false;
e.printStackTrace();
throw new SQLException(" UNABLE TO FETCH INSERT");
}
return flag;
}
maximum open cursor is set to 4000
code is executing
update tableA set colA ='x',lst_upd_date = trunc(sysdate) where trunc(date) = to_date('"+date+"','dd-mm-yyyy')update query for around 8000 times
最大打开游标设置为 4000 代码正在执行
update tableA set colA ='x',lst_upd_date = trunc(sysdate) where trunc(date) = to_date('"+date+"','dd-mm-yyyy')更新查询大约 8000 次
but after around 2000 days its throwing exception as "maximum open cursors exceeded"
但在大约 2000 天后，它的抛出异常为“超过最大打开游标数”
please suggest code changes for this. @TimBiegeleisen here is the code get connecttion
请为此建议代码更改。@TimBiegeleisen 这里是获取连接的代码
public Connection getConnection(String sessId)
{
Connection connection=null;
setLastAccessed(System.currentTimeMillis());
connection=(Connection)sessionCon.get(sessId);
try
{
if(connection==null || connection.isClosed() )
{
if ( ds == null )
{
InitialContext ic = new InitialContext();
ds = (DataSource) ic.lookup("java:comp/env/iislDB");
}
connection=ds.getConnection();
sessionCon.put(sessId, connection);
}
}
catch (SQLException e)
{
e.printStackTrace();
}
catch (Exception e)
{
e.printStackTrace();
}
return connection;
}
`
`
error stack is as bellow
错误堆栈如下
java.sql.SQLException: ORA-01000: maximum open cursors exceeded
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
at oracle.jdbc.ttc7.Oopen.receive(Oopen.java:118)
at oracle.jdbc.ttc7.TTC7Protocol.open(TTC7Protocol.java:472)
at oracle.jdbc.driver.OracleStatement.<init>(OracleStatement.java:499)
at oracle.jdbc.driver.OracleConnection.privateCreateStatement(OracleConnection.java:683)
at oracle.jdbc.driver.OracleConnection.createStatement(OracleConnection.java:560)
at org.apache.tomcat.dbcp.dbcp.DelegatingConnection.createStatement(DelegatingConnection.java:257)
at org.apache.tomcat.dbcp.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.createStatement(PoolingDataSource.java:216)
at com.iisl.business.adminbo.computeindex.MoviIndexComputeBO.calculateMoviValue(MoviIndexComputeBO.java:230)
回答by Ammad
One of quickest solution is to increase cursor that each connection can handle by issuing following command on SQL prompt:
最快的解决方案之一是通过在 SQL 提示符下发出以下命令来增加每个连接可以处理的游标：
alter system set open_cursors = 1000
Also, add finally block in your code and close the connection to help closing cursors when ever exception occurs.
此外，在您的代码中添加 finally 块并关闭连接以帮助在发生异常时关闭游标。
Also, run this query to see where actually cursor are opened.
此外，运行此查询以查看实际打开游标的位置。
select sid ,sql_text, count(*) as "OPEN CURSORS", USER_NAME from v$open_cursor
finally {
if (connection!=null) {
connection.close();
}
回答by Justin Cave
Your code has a cursor leak. That's what is causing the error. It seems unlikely that your code can really go 2000 days (about 5.5 years) before encountering the error. If that was the case, I'd wager that you'd be more than happy to restart a server twice a decade.
您的代码存在游标泄漏。这就是导致错误的原因。在遇到错误之前，您的代码似乎不太可能真正运行 2000 天（大约 5.5 年）。如果是这样的话，我敢打赌你会非常乐意每十年重启两次服务器。
In your tryblock, you create a Statement. If an exception is thrown between the time that the statement is created and the time that st.close()is called, your code will leave the statement open and you will have leaked a cursor. Once a session has leaked 4000 cursors, you'll get the error. Increasing max_open_cursorswill merely delay when the error occurs, it won't fix the underlying problem.
在您的try块中，您创建了一个Statement. 如果在创建语句和st.close()调用语句之间抛出异常，您的代码将使语句保持打开状态，并且您将泄漏游标。一旦会话泄漏了 4000 个游标，您就会收到错误消息。增加max_open_cursors只会在错误发生时延迟，它不会解决根本问题。
The underlying problem is that your try/ catchblock needs a finallythat closes the Statementif it was left open by the try. For this to work, you'd need to declare stoutside of the try
根本的问题是，你的try/ catch模块需要一个finally封闭的Statement，如果它是开着由try。为此，您需要st在try
finally {
if (st != null) {
st.close();
}
}
回答by Jean de Lavarene
As mentioned in another response you will leak cursors if an exception is thrown during the statement execution because st.close()won't be executed. You can use Java's try-with-resources syntax to be sure that your statement object is closed:
如另一个响应中所述，如果在语句执行期间抛出异常，您将泄漏游标，因为st.close()不会执行。您可以使用 Java 的 try-with-resources 语法来确保您的语句对象已关闭：
try (Statement st = con.createStatement())
{
flag=st.execute(query);
flag=true;
}
catch (Exception e)
{
flag=false;
e.printStackTrace();
throw new SQLException(" UNABLE TO FETCH INSERT");
}
return flag;
