java.sql.SQLException: - ORA-01000: 超出最大打开游标数 [英] java.sql.SQLException: - ORA-01000: maximum open cursors exceeded

问题描述

我收到一个 ORA-01000 SQL 异常.所以我有一些相关的问题.

I am getting an ORA-01000 SQL exception. So I have some queries related to it.

1. 最大打开游标数是否与 JDBC 连接数完全相关，或者它们是否也与我们为单个连接创建的语句和结果集对象相关?(我们正在使用连接池)
2. 有没有办法配置数据库中语句/结果集对象的数量(如连接)?
3. 是否建议在单线程环境中使用实例变量语句/结果集对象而不是方法本地语句/结果集对象?

4. 在循环中执行准备好的语句会导致这个问题吗?(当然，我可以使用 sqlBatch) 注意:一旦循环结束，pStmt 就会关闭.

1. Are maximum open cursors exactly related to number of JDBC connections, or are they also related to the statement and resultset objects we have created for a single connection ? (We are using pool of connections)
2. Is there a way to configure the number of statement/resultset objects in the database (like connections) ?
3. Is it advisable to use instance variable statement/resultset object instead of method local statement/resultset object in a single threaded environment ?

4. Does executing a prepared statement in a loop cause this issue ? (Of course, I could have used sqlBatch) Note: pStmt is closed once loop is over.

{ //method try starts  
  String sql = "INSERT into TblName (col1, col2) VALUES(?, ?)";
  pStmt = obj.getConnection().prepareStatement(sql);
  pStmt.setLong(1, subscriberID);
  for (String language : additionalLangs) {
    pStmt.setInt(2, Integer.parseInt(language));
    pStmt.execute();
  }
} //method/try ends

{ //finally starts
   pStmt.close()
} //finally ends 

如果在单个连接对象上多次调用 conn.createStatement() 和 conn.prepareStatement(sql) 会发生什么?

What will happen if conn.createStatement() and conn.prepareStatement(sql) are called multiple times on single connection object ?

编辑 1:6. 使用弱/软引用语句对象是否有助于防止泄漏?

6. Will the use of Weak/Soft reference statement object help in preventing the leakage ?

编辑 2:1. 有什么办法可以在我的项目中找到所有丢失的statement.close()"?我知道这不是内存泄漏.但是我需要找到一个符合垃圾收集条件的语句引用(不执行 close() 的地方)?任何可用的工具?还是我必须手动分析它?

1. Is there any way, I can find all the missing "statement.close()"s in my project ? I understand it is not a memory leak. But I need to find a statement reference (where close() is not performed) eligible for garbage collection ? Any tool available ? Or do I have to analyze it manually ?

请帮我理解.

转到 ORACLE 机器并以 sysdba 身份启动 sqlplus.

Go to ORACLE machine and start sqlplus as sysdba.

[oracle@db01 ~]$ sqlplus / as sysdba 

然后运行

SELECT   A.VALUE,
    S.USERNAME,
    S.SID,
    S.SERIAL#
  FROM V$SESSTAT A,
    V$STATNAME B,
    V$SESSION S
  WHERE A.STATISTIC# = B.STATISTIC#
    AND S.SID        = A.SID
    AND B.NAME       = 'opened cursors current'
    AND USERNAME     = 'VELU';

如果可能，请阅读我的回答以进一步了解我的解决方案

推荐答案

ORA-01000，最大开放游标错误，是 Oracle 数据库开发中极其常见的错误.在 Java 上下文中，当应用程序尝试打开的 ResultSet 比数据库实例上配置的游标数多时，就会发生这种情况.

ORA-01000, the maximum-open-cursors error, is an extremely common error in Oracle database development. In the context of Java, it happens when the application attempts to open more ResultSets than there are configured cursors on a database instance.

常见原因有:

1. 配置错误

1. Configuration mistake

• 您的应用程序中查询数据库的线程多于数据库上的游标.一种情况是您的连接和线程池大于数据库上的游标数量.
• 您有许多开发人员或应用程序连接到同一个数据库实例(其中可能包含许多架构)，并且您一起使用了过多的连接.

• 解决方案:

• 增加数据库上的游标数量(如果资源允许)或
• 减少应用程序中的线程数.

光标泄漏

• 应用程序没有关闭 ResultSet(在 JDBC 中)或游标(在数据库的存储过程中)
• 解决方案:光标泄漏是错误；增加 DB 上的游标数量只会延迟不可避免的失败.可以使用静态代码分析、JDBC 或应用程序级日志记录，以及 数据库监控.

• The applications is not closing ResultSets (in JDBC) or cursors (in stored procedures on the database)
• Solution: Cursor leaks are bugs; increasing the number of cursors on the DB simply delays the inevitable failure. Leaks can be found using static code analysis, JDBC or application-level logging, and database monitoring.

背景

本节描述了游标背后的一些理论以及应该如何使用 JDBC.如果您不需要了解背景，则可以跳过此部分，直接进入消除泄漏".

Background

This section describes some of the theory behind cursors and how JDBC should be used. If you don't need to know the background, you can skip this and go straight to 'Eliminating Leaks'.

游标是数据库上的一种资源，用于保存查询的状态，特别是读取器在 ResultSet 中的位置.每个 SELECT 语句都有一个游标，PL/SQL 存储过程可以根据需要打开和使用任意数量的游标.您可以在 Orafaq 上找到有关游标的更多信息.

A cursor is a resource on the database that holds the state of a query, specifically the position where a reader is in a ResultSet. Each SELECT statement has a cursor, and PL/SQL stored procedures can open and use as many cursors as they require. You can find out more about cursors on Orafaq.

一个数据库实例通常服务于几个不同的模式，许多不同的用户，每个用户都有多个会话.为此，它具有可用于所有模式、用户和会话的固定数量的游标.当所有游标都打开(正在使用)并且请求需要新游标时，请求将失败并显示 ORA-010000 错误.

A database instance typically serves several different schemas, many different users each with multiple sessions. To do this, it has a fixed number of cursors available for all schemas, users and sessions. When all cursors are open (in use) and request comes in that requires a new cursor, the request fails with an ORA-010000 error.

该号码通常由 DBA 在安装时配置.当前使用的游标数量、最大数量和配置可以在Oracle SQL 开发人员.从 SQL 可以设置为:

The number is normally configured by the DBA on installation. The number of cursors currently in use, the maximum number and the configuration can be accessed in the Administrator functions in Oracle SQL Developer. From SQL it can be set with:

ALTER SYSTEM SET OPEN_CURSORS=1337 SID='*' SCOPE=BOTH;

将 JVM 中的 JDBC 与 DB 上的游标相关联

下面的 JDBC 对象与以下数据库概念紧密耦合:

Relating JDBC in the JVM to cursors on the DB

The JDBC objects below are tightly coupled to the following database concepts:

• JDBC Connection 是数据库会话的客户端表示，并提供数据库事务.一个连接在任何时候只能打开一个事务(但事务可以嵌套)
• 数据库上的单个游标支持 JDBC ResultSet.在 ResultSet 上调用 close() 时，会释放游标.
• JDBC CallableStatement 调用数据库上的存储过程，通常用 PL/SQL 编写.存储过程可以创建零个或多个游标，并且可以将游标作为 JDBC ResultSet 返回.

• JDBC Connection is the client representation of a database session and provides database transactions. A connection can have only a single transaction open at any one time (but transactions can be nested)
• A JDBC ResultSet is supported by a single cursor on the database. When close() is called on the ResultSet, the cursor is released.
• A JDBC CallableStatement invokes a stored procedure on the database, often written in PL/SQL. The stored procedure can create zero or more cursors, and can return a cursor as a JDBC ResultSet.

JDBC 是线程安全的:可以在线程之间传递各种 JDBC 对象.

JDBC is thread safe: It is quite OK to pass the various JDBC objects between threads.

比如你可以在一个线程中创建连接；另一个线程可以使用此连接创建 PreparedStatement，第三个线程可以处理结果集.唯一的主要限制是在任何时候都不能在单个 PreparedStatement 上打开多个 ResultSet.请参阅Oracle DB 是否支持每个连接多个(并行)操作?

For example, you can create the connection in one thread; another thread can use this connection to create a PreparedStatement and a third thread can process the result set. The single major restriction is that you cannot have more than one ResultSet open on a single PreparedStatement at any time. See Does Oracle DB support multiple (parallel) operations per connection?

请注意，数据库提交发生在连接上，因此该连接上的所有 DML(插入、更新和删除)将一起提交.因此，如果要同时支持多个事务，那么每个并发事务必须至少有一个Connection.

Note that a database commit occurs on a Connection, and so all DML (INSERT, UPDATE and DELETE's) on that connection will commit together. Therefore, if you want to support multiple transactions at the same time, you must have at least one Connection for each concurrent Transaction.

执行 ResultSet 的典型示例是:

A typical example of executing a ResultSet is:

Statement stmt = conn.createStatement();
try {
    ResultSet rs = stmt.executeQuery( "SELECT FULL_NAME FROM EMP" );
    try {
        while ( rs.next() ) {
            System.out.println( "Name: " + rs.getString("FULL_NAME") );
        }
    } finally {
        try { rs.close(); } catch (Exception ignore) { }
    }
} finally {
    try { stmt.close(); } catch (Exception ignore) { }
}

注意 finally 子句如何忽略 close() 引发的任何异常:

Note how the finally clause ignores any exception raised by the close():

• 如果您只是在没有 try {} catch {} 的情况下关闭 ResultSet，它可能会失败并阻止 Statement 被关闭
• 我们希望允许 try 主体中引发的任何异常传播给调用者.如果您有一个循环，例如创建和执行 Statements，请记住关闭循环中的每个 Statement.

在 Java 7 中，Oracle 引入了 AutoCloseable 接口 用一些不错的语法糖替换了大部分 Java 6 样板.

In Java 7, Oracle has introduced the AutoCloseable interface which replaces most of the Java 6 boilerplate with some nice syntactic sugar.

JDBC 对象可以安全地保存在局部变量、对象实例和类成员中.通常更好的做法是:

JDBC objects can be safely held in local variables, object instance and class members. It is generally better practice to:

• 使用对象实例或类成员来保存在较长时间内多次重复使用的 JDBC 对象，例如 Connections 和 PreparedStatements
• 对 ResultSet 使用局部变量，因为这些变量通常在单个函数的范围内获得、循环然后关闭.

但是，有一个例外:如果您使用 EJB 或 Servlet/JSP 容器，则必须遵循严格的线程模型:

There is, however, one exception: If you are using EJBs, or a Servlet/JSP container, you have to follow a strict threading model:

• 只有应用服务器创建线程(用它来处理传入的请求)
• 只有应用服务器创建连接(您从连接池中获取)
• 在调用之间保存值(状态)时，您必须非常小心.永远不要将值存储在您自己的缓存或静态成员中 - 这在集群和其他奇怪的情况下是不安全的，并且应用程序服务器可能会对您的数据做出可怕的事情.而是使用有状态 bean 或数据库.
• 特别是，从不通过不同的远程调用保存 JDBC 对象(连接、结果集、准备语句等)——让应用服务器管理它.应用服务器不仅提供连接池，还缓存您的 PreparedStatements.

• Only the Application Server creates threads (with which it handles incoming requests)
• Only the Application Server creates connections (which you obtain from the connection pool)
• When saving values (state) between calls, you have to be very careful. Never store values in your own caches or static members - this is not safe across clusters and other weird conditions, and the Application Server may do terrible things to your data. Instead use stateful beans or a database.
• In particular, never hold JDBC objects (Connections, ResultSets, PreparedStatements, etc) over different remote invocations - let the Application Server manage this. The Application Server not only provides a connection pool, it also caches your PreparedStatements.

有许多流程和工具可用于帮助检测和消除 JDBC 泄漏:

There are a number of processes and tools available for helping detect and eliminating JDBC leaks:

1. 在开发过程中 - 尽早发现错误是迄今为止最好的方法:

1. During development - catching bugs early is by far the best approach:

1. 开发实践:良好的开发实践应该在软件离开开发人员办公桌之前减少软件中的错误数量.具体做法包括:

1. Development practices: Good development practices should reduce the number of bugs in your software before it leaves the developer's desk. Specific practices include:

1. 结对编程，教育那些没有足够经验的人
2. 代码审查，因为多眼比单眼好
3. 单元测试，这意味着您可以从测试工具中练习任何和所有代码库这使得重现泄漏变得微不足道
4. 使用现有库进行连接池而不是构建你自己的

1. Pair programming, to educate those without sufficient experience
2. Code reviews because many eyes are better than one
3. Unit testing which means you can exercise any and all of your code base from a test tool which makes reproducing leaks trivial
4. Use existing libraries for connection pooling rather than building your own

静态代码分析:使用像优秀的Findbugs这样的工具来执行静态代码分析.这会找出许多没有正确处理 close() 的地方.Findbugs 有一个 Eclipse 插件，但它也可以一次性独立运行，集成到 Jenkins CI 和其他构建工具中

Static Code Analysis: Use a tool like the excellent Findbugs to perform a static code analysis. This picks up many places where the close() has not been correctly handled. Findbugs has a plugin for Eclipse, but it also runs standalone for one-offs, has integrations into Jenkins CI and other build tools

运行时:

At runtime:

1. 可持有性和提交

1. Holdability and commit

1. 如果 ResultSet 的可保持性是 ResultSet.CLOSE_CURSORS_OVER_COMMIT，则在调用 Connection.commit() 方法时关闭 ResultSet.这可以使用 Connection.setHoldability() 或使用重载的 Connection.createStatement() 方法设置.

运行时记录.

Logging at runtime.

1. 在您的代码中加入好的日志语句.这些内容应该清晰易懂，以便客户、支持人员和团队成员无需培训即可理解.它们应该简洁，包括打印关键变量和属性的状态/内部值，以便您可以跟踪处理逻辑.良好的日志记录是调试应用程序的基础，尤其是那些已部署的应用程序.

2. 您可以将调试 JDBC 驱动程序添加到您的项目中(用于调试 - 不要实际部署它).一个例子(我没有用过)是 log4jdbc.然后你需要对这个文件做一些简单的分析，看看哪些执行没有相应的关闭.计算打开和关闭应该突出显示是否存在潜在问题

1. Put good log statements in your code. These should be clear and understandable so the customer, support staff and teammates can understand without training. They should be terse and include printing the state/internal values of key variables and attributes so that you can trace processing logic. Good logging is fundamental to debugging applications, especially those that have been deployed.

2. You can add a debugging JDBC driver to your project (for debugging - don't actually deploy it). One example (I have not used it) is log4jdbc. You then need to do some simple analysis on this file to see which executes don't have a corresponding close. Counting the open and closes should highlight if there is a potential problem

1. 监控数据库.使用 SQL Developer 的Monitor SQL"功能或 Quest 的 TOAD 等工具来监控您正在运行的应用程序.这篇文章中描述了监控.在监视期间，您查询打开的游标(例如从表 v$sesstat)并查看它们的 SQL.如果游标的数量在增加，并且(最重要的是)被一个相同的 SQL 语句支配，那么您就会知道该 SQL 存在泄漏.搜索您的代码并查看.

1. Monitoring the database. Monitor your running application using the tools such as the SQL Developer 'Monitor SQL' function or Quest's TOAD. Monitoring is described in this article. During monitoring, you query the open cursors (eg from table v$sesstat) and review their SQL. If the number of cursors is increasing, and (most importantly) becoming dominated by one identical SQL statement, you know you have a leak with that SQL. Search your code and review.

其他想法

你能用 Wea​​kReferences 来处理关闭连接吗?

弱引用和软引用是允许您以允许 JVM 在其认为合适的任何时间对所指对象进行垃圾回收的方式来引用对象的方法(假设该对象没有强引用链).

Other thoughts

Can you use WeakReferences to handle closing connections?

Weak and soft references are ways of allowing you to reference an object in a way that allows the JVM to garbage collect the referent at any time it deems fit (assuming there are no strong reference chains to that object).

如果在构造函数中将一个 ReferenceQueue 传递给软引用或弱引用，则该对象在它发生时(如果它发生了)被 GC 处理时会被放置在 ReferenceQueue 中.使用这种方法，您可以与对象的终结进行交互，并且您可以在那一刻关闭或终结对象.

If you pass a ReferenceQueue in the constructor to the soft or weak Reference, the object is placed in the ReferenceQueue when the object is GC'ed when it occurs (if it occurs at all). With this approach, you can interact with the object's finalization and you could close or finalize the object at that moment.

幻影引用有点奇怪；它们的目的只是控制终结，但你永远无法获得对原始对象的引用，因此很难对其调用 close() 方法.

Phantom references are a bit weirder; their purpose is only to control finalization, but you can never get a reference to the original object, so it's going to be hard to call the close() method on it.

然而，尝试控制何时运行 GC 很少是一个好主意(Weak、Soft 和 PhantomReferences 让您在事实之后知道对象已排队等待 GC).事实上，如果JVM 中的内存量很大(例如-Xmx2000m)，您可能永远 GC 对象，并且您仍然会遇到ORA-01000.如果 JVM 内存相对于您的程序的要求而言较小，您可能会发现 ResultSet 和 PreparedStatement 对象在创建后立即被 GC(在您可以读取它们之前)，这可能会使您的程序失败.

However, it is rarely a good idea to attempt to control when the GC is run (Weak, Soft and PhantomReferences let you know after the fact that the object is enqueued for GC). In fact, if the amount of memory in the JVM is large (eg -Xmx2000m) you might never GC the object, and you will still experience the ORA-01000. If the JVM memory is small relative to your program's requirements, you may find that the ResultSet and PreparedStatement objects are GCed immediately after creation (before you can read from them), which will likely fail your program.

TL;DR:弱引用机制不是管理和关闭 Statement 和 ResultSet 对象的好方法.

TL;DR: The weak reference mechanism is not a good way to manage and close Statement and ResultSet objects.

这篇关于java.sql.SQLException: - ORA-01000: 超出最大打开游标数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！