ORA-1000: Maximum Open Cursors Exceeded

SYMPTOM

The client application receives an ORA-1000 error. The application will have usually aborted and the occurrences of this error increase with amount of data being processed.

ORACLE ERROR (oerr) OUTPUT

oerr ora 1000

01000, 00000, "maximum open cursors exceeded"
// *Cause:
// *Action:

CAUSE

A common cause of this problem is a record set being opened and not closed before another one is opened.

An example of this in pseudo code:

public void doStuff() {

    ResultSet rs = null;

    rs = tempStatement.executeQuery();
    ….
    rs = tempStatement2.executeQuery();
    ….
    rs = tempStatement3.executeQuery();

    rs.close()    

}

While the above looks OK, it actually leaves 2 deference record sets open in the background.

Another, even worse example, again in pseudo code:

public void doStuff() {

        ResultSet rs = null;

        for(i=1, i<bigNumber; i++) {

             Statement tempStatement = new Statement(“SQL”);

             rs = tempStatement.executeQuery();

        }

    rs.close()    
}

Here, this leaves bigNumber-1 deference record sets open in the background.

If at any point the total number of open record sets (including those dereferences) exceeds the Oracle open_cursors setting then an exception will occur.

The risk this introduces to an application is to provide unstable code that will not scale.

SOLUTION

Ensure that the java programmers close the record sets BEFORE dereferencing them.

© copyright 2001-2014 ABCdba.com | all rights reserved