Comprehensive Analysis of ORA-01000: Maximum Open Cursors Exceeded and Solutions

Problem Overview

The ORA-01000 error is an extremely common issue in Oracle database development, occurring when an application attempts to open more ResultSets than the number of cursors configured on the database instance. In Java environments, this typically manifests as a java.sql.SQLException.

Cursor Fundamentals

A cursor is a resource on the database that maintains the state of a query, specifically the position where a reader is in a ResultSet. Each SELECT statement corresponds to a cursor, and PL/SQL stored procedures can open and use multiple cursors as required.

A database instance typically serves multiple different schemas, users, and sessions. For this purpose, it provides a fixed number of available cursors for all schemas, users, and sessions. When all cursors are open (in use) and a request arrives that requires a new cursor, the request fails with an ORA-01000 error.

Common Error Causes Analysis

Configuration Mistakes

The number of threads in the application querying the database exceeds the number of cursors on the database. Common scenarios include connection pools and thread pools larger than the database cursor count, or multiple developers or applications connected to the same database instance collectively using too many connections.

Solution: Increase the number of cursors on the database if resources allow, or reduce the number of threads in the application.

Cursor Leaks

The application fails to properly close ResultSets (in JDBC) or cursors (in database stored procedures). Cursor leaks are program defects; simply increasing the number of cursors on the database only delays the inevitable failure.

Solution: Use static code analysis, JDBC or application-level logging, and database monitoring to detect leaks.

Relationship Between JDBC and Database Cursors

There is a tight coupling between JDBC objects and 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)
• JDBC ResultSet is supported by a single cursor on the database. When close() is called on the ResultSet, the cursor is released
• 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 perfectly acceptable to pass 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.

JDBC Object Closing Best Practices

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.

JDBC Object Holding Strategies

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

Methods for Eliminating Leaks

Development Phase Detection

Catching bugs early during development is the best approach:

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 pair programming, code reviews, unit testing, and using existing connection pooling libraries
2. Static Code Analysis: Use excellent tools like Findbugs to perform static code analysis. This picks up many places where the close() has not been correctly handled

Runtime Detection

1. 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
2. Runtime Logging: Put good log statements in your code. These should be clear and understandable so the customer, support staff and teammates can understand without training
3. Database Monitoring: Monitor your running application using tools such as the SQL Developer 'Monitor SQL' function or Quest's TOAD. During monitoring, query the open cursors (e.g., 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

Configuration and Diagnostic Methods

The number of cursors is normally configured by the DBA during installation. You can use the following SQL statements to identify the actual number of open cursors configured and the number of cursors taken by the process on the Oracle side:

show parameter open_cursors

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='<INSERT-DATABASE-USERNAME-HERE>'

Solutions and Workarounds

Oracle's recommendation is that JDBC driver version should always be either same as or higher than the Oracle database version in order to leverage the latest capabilities of the driver. Hence, our recommendation is that you upgrade your Oracle JDBC Driver to the latest one available in the Oracle JDBC Downloads page. This might help to fix any already identified JDBC Driver bug that could cause an open cursors leak.

As an alternative, you may want to increase the maximum open cursor in the database by executing the following SQL command on the Oracle side:

ALTER SYSTEM SET open_cursors = 1000 SCOPE=BOTH;

This example sets the maximum open cursors to 1000. Change this value as required. Please check with your Oracle DBA before implementing any change in this regard.

Special Scenario Analysis

Executing PreparedStatement in Loops

Executing PreparedStatement in loops can indeed cause cursor issues, even if pStmt is closed once the loop is over. Each execution consumes database resources and may reach cursor limits during high-volume iterations.

Multiple Statement Object Creation

Calling conn.createStatement() and conn.prepareStatement(sql) multiple times on a single connection object results in multiple Statement objects, each requiring individual closure; otherwise, cursor leakage occurs.

Applicability of Weak and Soft References

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).

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

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