从字符串中拆分特定的数字链(Split specific chain of digits from a string)

 最大的开放游标错误ORA-01000是Oracle数据库开发中极为常见的错误。 在Java的上下文中，当应用程序尝试打开比数据库实例上配置的游标更多的ResultSet时，会发生这种情况。 
 
 常见原因有： 
 
 
 
 配置错误 
 
  
 
   
 您的应用程序中查询数据库的线程多于DB上的游标。 一种情况是您的连接和线程池大于数据库上的游标数量。 
 
   
 您有许多开发人员或应用程序连接到同一个DB实例（可能包含许多模式），并且您一起使用的连接太多。 
 
   
 解： 
 
    
 
     
 增加数据库上的游标数量 （如果资源允许）或 
 
     
 降低应用程序中的线程数。 
 
    
 
  
 
 
 光标泄漏 
 
  
 
   
 应用程序未关闭ResultSet（在JDBC中）或游标（在数据库的存储过程中） 
 
   
 解决方案 ：游标泄漏是错误; 增加数据库上的游标数量只会延迟不可避免的失败。 可以使用静态代码分析 ， JDBC或应用程序级日志记录和数据库监控来找到漏洞。 
 
  
 
 
 背景 
 
 本节介绍了游标背后的一些理论，以及如何使用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 Connection是数据库会话的客户端表示形式，并提供数据库事务 。 连接只能在任何一个时间打开一个事务（但事务可以嵌套） 
 
 
 数据库上的单个游标支持JDBC ResultSet 。 当在ResultSet上调用close（）时，光标被释放。 
 
 
 JDBC CallableStatement调用数据库上的存储过程 ，通常用PL / SQL编写。 存储过程可以创建零个或多个游标，并可以将游标作为JDBC ResultSet返回。 
 
 
 JDBC是线程安全的：可以在线程之间传递各种JDBC对象。 
 
 例如，您可以在一个线程中创建连接; 另一个线程可以使用此连接创建一个PreparedStatement，第三个线程可以处理结果集。 单一的主要限制是您不能随时在单个PreparedStatement上打开多个ResultSet。 请参阅Oracle DB是否支持多个（并行）操作？ 
 
 请注意，连接上发生数据库提交，因此该连接上的所有DML（INSERT，UPDATE和DELETE）将一起提交。 因此，如果要同时支持多个事务，则每个并发事务必须至少有一个连接。 
 
 关闭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关闭 
 
 
 我们希望允许尝试的正文中引发的异常传播给调用者。 如果您有循环，例如创建和执行语句，请记住关闭循环中的每个Statement。 
 
 
 在Java 7中，Oracle引入了AutoCloseable接口 ，该接口用一些很好的语法糖代替了大部分Java 6样板。 
 
 保存JDBC对象 
 
 JDBC对象可以安全地保存在局部变量，对象实例和类成员中。 一般较好的做法是： 
 
 
 
 使用对象实例或类成员来容纳在更长时间内重复使用多次的JDBC对象，如Connections和PreparedStatements 
 
 
 为ResultSet使用局部变量，因为这些变量是获取的，循环遍历，然后通常在单个函数的范围内关闭。 
 
 
 但是有一个例外：如果您正在使用EJB或Servlet / JSP容器，则必须遵循严格的线程模型： 
 
 
 
 只有应用程序服务器创建线程（用于处理传入请求） 
 
 
 只有应用程序服务器创建连接（您从连接池获取） 
 
 
 在保存呼叫之间的值（状态）时，必须非常小心。 不要在自己的缓存或静态成员中存储值 - 这在群集和其他奇怪的条件下是不安全的，应用服务器可能会对您的数据造成可怕的影响。 而是使用有状态的bean或数据库。 
 
 
 特别是， 不要通过不同的远程调用来持有JDBC对象（Connections，ResultSet，PreparedStatements等） - 让应用服务器管理这个。 应用程序服务器不仅提供连接池，还可以缓存您的PreparedStatements。 
 
 
 消除泄漏 
 
 有许多进程和工具可用于帮助检测和消除JDBC泄漏： 
 
 
 
 在开发过程中，早期发现bug是迄今为止最好的方法： 
 
  
 
   
 开发实践：良好的开发实践应该在您离开开发人员桌面之前减少软件中的错误数量。 具体做法包括： 
 
    
 
     
 配对编程 ，教育没有足够经验的人 
 
     
 代码评论，因为许多眼睛比一个更好 
 
     
 单元测试 ，这意味着您可以从测试工具中执行任何和所有的代码库，这使得再现漏洞变得微不足道 
 
     
 使用现有的库进行连接池，而不是建立自己的库 
 
    
 
   
 静态代码分析：使用像优秀的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的TOAD等工具来监视正在运行的应用程序。 本文介绍了监控。 在监视期间，您可以查询打开的游标（例如从表v $ sesstat），并查看其SQL。 如果游标数量在增加，而且（最重要的是）由一个相同的SQL语句控制，那么你知道你有一个漏洞。 搜索您的代码并查看。 
 
      
 
    
 
  
 
 
 其他想法 
 
 你可以使用WeakReferences处理关闭连接吗？ 
 
 弱和软引用是允许您以允许JVM在其认为合适的任何时间垃圾收集指示对象的方式引用对象的方式（假设该对象没有强引用链）。 
 
 如果将构造函数中的ReferenceQueue传递给软引用或弱引用，则当对象在发生时（如果发生）发生GC时，该对象将放置在ReferenceQueue中。 通过这种方法，您可以与对象的最终化交互进行交互，您可以在该时间关闭或完成对象。 
 
 幻影参考有点威慑; 他们的目的只是为了控制最终化，但你永远不会得到对原始对象的引用，所以很难调用close（）方法。 
 
 然而，尝试控制GC运行时，很少有一个好主意（弱，软和PhantomReferences让您知道对象为GC排队的事实 ）。 实际上，如果JVM中的内存量很大（例如-Xmx2000m），那么您可能永远不会 GC对象，您仍然会体验到ORA-01000。 如果JVM内存相对于程序的要求较小，则可能会发现ResultSet和PreparedStatement对象在创建之后立即被GCed（在您可以读取之前），这可能会导致程序失败。 
 
 TL; DR：弱引用机制不是管理和关闭Statement和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.
 
Common causes are:
 
 
 
Configuration mistake
 
  
 
   
You have more threads in your application querying the database than cursors on the DB. One case is where you have a connection and thread pool larger than the number of cursors on the database.
 
   
You have many developers or applications connected to the same DB instance (which will probably include many schemas) and together you are using too many connections.
 
   
Solution: 
 
    
 
     
Increasing the number of cursors on the database (if resources allow) or 
 
     
Decreasing the number of threads in the application.
 
    
 
  
 
 
Cursor leak
 
  
 
   
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. 
 
  
 
 
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'.
 
What is a cursor?
 
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.
 
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.
 
Finding and setting the number of cursors
 
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;
 
Relating JDBC in the JVM to cursors on the DB
 
The JDBC objects below are tightly coupled to the following database concepts:
 
 
 
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 is thread safe: It is quite OK to pass the various JDBC objects between threads. 
 
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?
 
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.
 
Closing JDBC objects
 
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) { }
}
 
Note how the finally clause ignores any exception raised by the close():
 
 
 
If you simply close the ResultSet without the try {} catch {}, it might fail and prevent the Statement being closed
 
 
We want to allow any exception raised in the body of the try to propagate to the caller. If you have a loop over, for example, creating and executing Statements, remember to close each Statement within the loop.
 
 
In Java 7, Oracle has introduced the AutoCloseable interface which replaces most of the Java 6 boilerplate with some nice syntactic sugar.
 
Holding JDBC objects
 
JDBC objects can be safely held in local variables, object instance and class members. It is generally better practice to:
 
 
 
Use object instance or class members to hold JDBC objects that are reused multiple times over a longer period, such as Connections and PreparedStatements
 
 
Use local variables for ResultSets since these are obtained, looped over and then closed typically within the scope of a single function. 
 
 
There is, however, one exception: If you are using EJBs, or a Servlet/JSP container, you have to follow a strict threading model:
 
 
 
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.
 
 
Eliminating leaks
 
There are a number of processes and tools available for helping detect and eliminating JDBC leaks:
 
 
 
During development - catching bugs early is by far the best approach:
 
  
 
   
Development practices: Good development practices should reduce the number of bugs in your software before it leaves the developer's desk. Specific practices include:
 
    
 
     
Pair programming, to educate those without sufficient experience
 
     
Code reviews because many eyes are better than one
 
     
Unit testing which means you can exercise any and all of your code base from a test tool which makes reproducing leaks trivial
 
     
Use existing libraries for connection pooling rather than building your own
 
    
 
   
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:
 
  
 
   
Holdability and commit
 
    
 
     
If the ResultSet holdability is ResultSet.CLOSE_CURSORS_OVER_COMMIT, then the ResultSet is closed when the Connection.commit() method is called. This can be set using Connection.setHoldability() or by using the overloaded Connection.createStatement() method. 
 
    
 
   
Logging at runtime. 
 
    
 
     
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. 
 
     
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
 
      
 
       
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.
 
      
 
    
 
  
 
 
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).
 
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.
 
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.
 
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: The weak reference mechanism is not a good way to manage and close Statement and ResultSet objects.