java - java.sql.SQLException:-ORA-01000:已超出最大打开游标

Question

我收到了ORA-01000 SQL异常。因此，我对此有一些疑问。


最大打开游标是否与JDBC连接的数量完全相关，还是与我们为单个连接创建的语句和结果集对象相关？ （我们正在使用连接池）
有没有一种方法可以配置数据库中的语句/结果集对象的数量（如连接）？
是否建议在单线程环境中使用实例变量statement / resultset对象而不是方法local statement / resultset对象？
在循环中执行准备好的语句是否会导致此问题？ （当然，我本可以使用sqlBatch的）注：一旦循环结束，pStmt将关闭。

{ //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）会发生什么？


编辑1：
6.使用弱/软引用声明对象是否有助于防止泄漏？

编辑2：
1.有什么办法可以在我的项目中找到所有缺少的“ statement.close（）”吗？我了解这不是内存泄漏。但是我需要找到一个符合垃圾回收条件的语句引用（不执行close（））？有没有可用的工具？还是我必须手动分析它？

请帮助我理解它。

解

在Oracle DB中为用户名-VELU查找打开的游标

转到ORACLE计算机，并以sysdba身份启动sqlplus。

[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';

如果可能，请阅读 my answer for more understanding of my solution

Answer 1

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

常见原因有：


配置错误


在应用程序中，查询数据库的线程比数据库中的游标更多。一种情况是您的连接和线程池大于数据库上的游标数。
您有许多开发人员或应用程序连接到同一个数据库实例（可能包含许多架构），并且一起使用的连接过多。
解：


数据库上的Increasing the number of cursors（如果资源允许）或
减少应用程序中的线程数。


游标泄漏


应用程序未关闭ResultSet（在JDBC中）或游标（在数据库上的存储过程中）
解决方案：游标泄漏是错误；增加数据库上的游标数量只会延迟不可避免的故障。可以使用static code analysis，JDBC或应用程序级日志记录和database monitoring查找泄漏。



背景

本节描述了游标背后的一些理论以及应如何使用JDBC。如果您不需要了解背景，可以跳过此步骤，直接进入“消除泄漏”。

什么是游标？

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

数据库实例通常服务于几种不同的模式，许多不同的用户各自具有多个会话。为此，它具有可用于所有模式，用户和会话的固定数量的游标。当所有游标都处于打开状态（使用中）并且要求新游标的请求进入时，请求失败，并出现ORA-010000错误。

查找和设置光标数量

该编号通常由DBA在安装时配置。可以在Oracle SQL Developer的管理员功能中访问当前使用的游标数，最大游标数和配置。在SQL中，可以使用以下命令进行设置：

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

将JVM中的JDBC与数据库上的游标相关联

下面的JDBC对象与以下数据库概念紧密相关：


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


JDBC是线程安全的：在线程之间传递各种JDBC对象是完全可以的。

例如，您可以在一个线程中创建连接。另一个线程可以使用此连接来创建PreparedStatement，第三个线程可以处理结果集。唯一的主要限制是您不能在一个PreparedStatement上同时打开多个ResultSet。见 Does Oracle DB support multiple (parallel) operations per connection?

请注意，数据库提交发生在连接上，因此该连接上的所有DML（INSERT，UPDATE和DELETE）都将一起提交。因此，如果要同时支持多个事务，则每个并发事务必须至少具有一个Connection。

关闭JDBC对象

执行ResultSet的典型示例是：

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（）引发的任何异常：


如果您只关闭ResultSet而没有使用try {} catch {}，则它可能会失败并阻止Statement被关闭
我们希望允许尝试主体中引发的任何异常传播到调用方。
如果有一个循环，例如创建和执行语句，请记住关闭循环中的每个语句。


在Java 7中，Oracle引入了 AutoCloseable interface，它用一些漂亮的语法糖代替了Java 6的大部分样板。

持有JDBC对象

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


使用对象实例或类成员来保存可在更长的时间内多次重用的JDBC对象，例如Connections和PreparedStatements
使用ResultSet的局部变量，因为它们通常是在单个函数的范围内获取，循环和关闭的。


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


只有Application Server创建线程（用于处理传入请求）
只有Application Server创建连接（您可以从连接池中获得）
在两次调用之间保存值（状态）时，必须非常小心。永远不要将值存储在您自己的缓存或静态成员中-这在群集和其他怪异条件下并不安全，Application Server可能会对您的数据造成严重影响。而是使用有状态的bean或数据库。
特别是，永远不要通过不同的远程调用来保存JDBC对象（连接，结果集，PreparedStatements等）-让Application Server对此进行管理。 Application Server不仅提供连接池，还缓存您的PreparedStatements。


消除泄漏

有许多可用于帮助检测和消除JDBC泄漏的过程和工具：


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


开发实践：良好的开发实践应在软件离开开发人员之前减少软件中的错误数量。具体做法包括：


Pair programming，对没有足够经验的人进行教育
Code reviews因为很多眼睛比一只更好
Unit testing意味着您可以通过测试工具来练习所有代码库，从而使重现泄漏变得微不足道
使用 existing libraries进行连接池，而不是构建自己的池

静态代码分析：使用出色的 Findbugs之类的工具来执行静态代码分析。这会拾取许多未正确处理close（）的地方。 Findbugs有一个适用于Eclipse的插件，但它也可以一次性运行，并已集成到Jenkins CI和其他构建工具中

在运行时：


可保持性和提交


如果ResultSet的可保存性为ResultSet.CLOSE_CURSORS_OVER_COMMIT，则在调用Connection.commit（）方法时关闭ResultSet。可以使用Connection.setHoldability（）或使用重载的Connection.createStatement（）方法进行设置。

在运行时记录。


在您的代码中放置良好的日志语句。这些内容应清晰易懂，以便客户，支持人员和队友无需培训即可理解。它们应简洁，并包括打印关键变量和属性的状态/内部值，以便您可以跟踪处理逻辑。良好的日志记录是调试应用程序（尤其是已部署的应用程序）的基础。
您可以在项目中添加调试JDBC驱动程序（用于调试-请勿实际部署它）。一个示例（我没有使用过）是 log4jdbc。然后，您需要对此文件进行一些简单的分析，以查看哪些执行没有相应的关闭。计算打开和关闭应该突出显示是否存在潜在问题


监视数据库。使用诸如SQL Developer“ Monitor SQL”功能或 Quest's TOAD之类的工具监视正在运行的应用程序。 this article中描述了监视。在监视期间，您查询打开的游标（例如，从表v $ sesstat中）并查看其SQL。如果游标的数量在增加，并且（最重要的是）由一个相同的SQL语句控制，则您知道该SQL泄漏。搜索您的代码并查看。





其他想法

您可以使用WeakReferences处理关闭的连接吗？

弱引用和软引用是允许您以允许JVM在它认为合适的任何时间对对象进行垃圾收集的方式来引用对象的方法（假定该对象没有强大的引用链）。

如果将构造函数中的ReferenceQueue传递给软引用或弱引用，则当对象发生时（如果根本发生），则将该对象放置在ReferenceQueue中。使用这种方法，您可以与对象的终结处理进行交互，并且可以在此时关闭或终结该对象。

幻像引用有些古怪；它们的目的仅是控制最终确定，但是您永远无法获得对原始对象的引用，因此很难在其上调用close（）方法。

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

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