Debugging maximum open cursors issue in Java code

If you have some legacy database operations in your java code, sometimes your application will stop working after a while. When you look at your server log, you find something like this:

java.sql.SQLException: ORA-01000: maximum open cursors exceeded

...

Usually you can temporarily solve the problem by restarting the server. But it will come back at you when database connection running out of open cursors again.

Root Cause

The issue here is that every database connection can only have a fixed number of open cursors (defined as the maximum open cursors in the Oracle database). When your code run a query statement, it will open a cursor; when you close the statement, the cursor will be closed. However, sometimes programmers want to run two different queries in the same block of code, and they forget  

to close the first one before using the second. For example:

    PreparedStatement statement = null;

    ResultSet result;

    try {

      // is user an alumnus

      statement = connection.prepareStatement(

          "SELECT package1.is_alum(?) FROM dual ");

      statement.setString(1, _id);

      result = statement.executeQuery();

      

      if (result.next()) {

        _isAlum = result.getInt(1)==1?true:false;

      }

      // is user a student

      statement = connection.prepareStatement(

          "SELECT package1.is_student(?) FROM dual ");

      statement.setString(1, _id);

      result = statement.executeQuery();

      

      if (result.next()) {

        _isStudent = result.getInt(1)==1?true:false;

      }

    }

    finally {

      if (statement != null) statement.close(); 

    } 

    

The problem is that even though we close the prepared statement at the end, the first statement to check if user is an alum is never closed - a leaked open cursor!

Debug 

So how do you debug such a problem? The server log usually will not tell you where the unclosed statement is. It just informs you that a certain database operation can not run because maximum open cursors are reached. You need to go to the database itself, run a query to find out the SQL statement with the must open cursors:

SELECT s.machine, oc.user_name, oc.sql_text, count(1) cnt
FROM V$OPEN_CURSOR OC, V$SESSION S
WHERE OC.SID = S.SID 
GROUP BY USER_NAME, SQL_TEXT, MACHINE
HAVING COUNT(1) > 9
ORDER BY COUNT(1) DESC

You will get a result like this:

machine      user_name  sql_text                                cnt
my.host.com  ADMIN SELECT package1.is_alum(:1 ) FROM dual 413

Now we know the problem is of this sql statement, so search your java code for a substring of the statement (i.e. search "package1.is_alum" in this case), and then close the statement!

Solution

    PreparedStatement statement = null;

    ResultSet result;

    try {

      // is user an alumnus

      statement = connection.prepareStatement(

          "SELECT package1.is_alum(?) FROM dual ");

      statement.setString(1, _id);

      result = statement.executeQuery();

      

      if (result.next()) {

        _isAlum = result.getInt(1)==1?true:false;

      }

      if (statement != null) statement.close();

      // is user a student

      statement = connection.prepareStatement(

          "SELECT package1.is_student(?) FROM dual ");

      statement.setString(1, _id);

      result = statement.executeQuery();

      

      if (result.next()) {

        _isStudent = result.getInt(1)==1?true:false;

      }

    }

    finally {

      if (statement != null) statement.close(); 

    }