[Oracle] ORA-01000: maximum open cursors exceeded Exception

oracle-logo

ORA-01000 is a common Oracle database exception which occurs during development. The exception defines the maximum open cursors (that has been set) for the database has already been reached. In the context of Java development, this normally is the result of application attempting to open more Resultset(s) and/or PreparedStatement(s) that the database can already handle.

Common causes of this error can be defined as follow:

  • The limit for the maximum open cursors has been set at a lower threshold.
  • The created Resultset (in JDBC) and/or cursors (in a PrepareStatement) are not being closed properly, which results in exhaustion.

In this post, we will look into cursors in greater detail, finding out about the state of the cursors in your Oracle database as well as how to tackle the ORA-01000 issue.

What is a Cursor (in an Oracle Database)?

A cursor is a resource that primarily acts as a pointer to fetch rows from a query that has been processed into a Resultset. Think of it as a data structure that holds results from a typical SQL SELECT statement:

A database may have many schemas with many tables, with many users defined for each schema and multiple sessions connecting to them at the same time. Thus, the cursors limit allows the database to set a fixed number of cursors to each schema, user and session. Once this limit has been reached and the next query requires another cursor, the database would throw out the ORA-01000 exception.

The Current State of the Open Cursors in an Oracle Database

To venture further into this issue, first off we need to look into the current state of the cursors in the database. The first query below shows us what is the current highest open cursor and the current open cursors threshold:

select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic# and b.name = 'opened cursors current'  and p.name= 'open_cursors'
group by p.value;

 

HIGHEST_OPEN_CUR      MAX_OPEN_CUR
----------------      ------------
69                    300

The result currently shows the current threshold for open cursors is set to 300, and currently there is a user using 69 cursors. Let’s look deeper as to which users are currently having these open cursors:

select sum(a.value) total_cur, avg(a.value) avg_cur,
max(a.value) max_cur, s.username, s.machine
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'
group by s.username, s.machine order by 1 desc

 

TOTAL_CUR   AVG_CUR     MAX_CUR     USERNAME    MACHINE
---------   -------     -------     --------    -------
114         14.25       69          A           KUL-0121
76          8.44        48          B           KUL-0121
29          1.38        18                      KUL-0121
26          8.67        13          C           xxx\KUL-0121

So, we can see that the user A is utilizing 69 open cursors. Upon checking, it seems that the user A is currently the user that is being used for a Java application which is configured to communicate with the Oracle database. If the Java application is not optimized to close the Resultset(s) and/or PreparedStatement(s), the value of 69 will be increased again.

TOTAL_CUR   AVG_CUR     MAX_CUR     USERNAME    MACHINE
---------   -------     -------     --------    -------
129         16.13       85          A           KUL-0121
75          8.33        47          B           KUL-0121
29          1.26        18                      KUL-0121
25          8.33        12          C           KUL-0121

So, What Can We Do About It?

Currently, we know the current state of the cursors in the database from the previous two SQL queries. Also, we have underlined two possible areas which can contribute to this. Let’s look into both of these areas, as well as what we can do to mitigate this issue.

  1. The limit for the maximum open cursors has been set at a lower threshold [Database Administrator]

    When you installed and configure an Oracle database with default settings, the threshold for open cursors would be set to 50. Oracle typically advises a value of 500 for most applications, and if your application is going to be accessed by many users, set this value to 1000.

    To implicitly set the open cursors limit, use the query below:

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

    The MAX_VALUE is the value you would like to set as a threshold for open cursors.

    Upon resetting the threshold, continue to monitor the open cursors count and try to find an average for this (using the query shown in the “The Current State of the Open Cursors in an Oracle Database” section above). If you see this value increasing, increase the threshold.

    Do note that this is a temporary mitigation step, as in most cases it may not be the threshold is too low, but the application itself is utilizing the open cursors count without purging unneeded ones.

  2. The created Resultset (in JDBC) and/or cursors (in a PrepareStatement) are not being closed properly, which results in exhaustion [Application Developer]

    The job as a developer in this context is to not blame this issue solely on the database administrator, as he only knows so much. Look through the source code of the application you are developing and analyse on how you can cleanly free up ResultSet(s) and/or PrepareStatment(s) that are not being used. This is to ensure that the open cursors are not being exhausted extensively.

    An example on how to execute a typical ResultSet is as follow:

    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) { }
    }
    

    Notice how the ResultSet and the Statement variables are being closed; regardless of whether it returns results or returns null. This ensures that once I’ve done with these variables, I would like to close them. This will signal to the database (via JDBC) that the block of code has been completed, and it can now purge the ResultSet (and frees two cursors).

    There are two more tips that you can consider in your application to ensure the exhaustion mechanism is kept at a minimum:

    • Use local ResultSet variables; since these can be used over and over within the same scope, and closed at the end of that scope.
    • Use class members to hold JDBC objects that will be used multiple times (such as Connection and PreparedStatment variables).

Final Word

We have looked into the concept of open cursors and what they are. We also looked into finding out the threshold for these cursors and how to fix them (both in a database administrator and the application developer’s point of views).

In short, the open cursors state in a database is a generic issue that exists in all large scale application developments. Hence, it is good to have a clear communication between the database administrator and the developer so that this issue can be ironed out at an acceptable level for the benefit of their users.


Further Reading


Was That a Good Read? Rate It!

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 4.00 out of 5)
Loading ... Loading ...

Add a Comment

Your email address will not be published. Required fields are marked *

Current day month ye@r *

CommentLuv badge