java jdbc and oracle - maximum open cursors exceeded

Question

I have some JDBC code that is retrieving data from Oracle.

In my method I use a prepared statement, however I don't close that prepared statement. To test it I ran this in a loop and sure enough I got an exception:

ORA-01000: maximum open cursors exceeded

My question is in case of a managed environment (code deployed on a Java EE Application Server using connection pools):

• What happens to the application?
• Will it never be able to fire any SQL query to the database unless the connection is closed / recycled? (assume that there is only 1 connection in the pool)

I'm assuming as connections in the pool are not really closed - the oracle session will be alive.

Solution 1

You need to close the ResultSet objects returned when you execute a query. And to make sure that you don't leak cursors, you need to do this in a finally block. I believe that this applies in a managed environment too.

Solution 2

You MUST close the Prepared Statement, otherwise you wont be able to execute more queries.

Suppose you have:

        PreparedStatement ps = conn.prepareStatement("select * from my_table");
        ps.execute();
        ps.close();

You must execute ps.close to avoid this issue.And as I @Stephen said, also close the ResultSet.

    ResultSet rs = ps.executeQuery();

    rs.close();
    ps.close();

Solution 3

The maximum open cursors error is generated on the oracle database. It is the database that has this limit. The application will keep sending the requests but the database will return the error.

When the maximum open cursors is exceeded, the application (i.e. the client) can still keep send the requests to the database but the database will just reject the request until the open cursors are closed.

You can increase the allowed open cursors using something like

“ALTER SYSTEM SET OPEN_CURSORS=2000 SID=’DUMMY’”;

But the above is not fixing the problem. To fix it you need to close your connections/resultsets/PreparedStatements etc.

One possible scenario when your app server will not be able to send SQL requests is if the number of allowed active connections on your connection pool is less than the number of connections the database allows.

Comments

[akila]

Thanks Ziggy so in a managed environment once I reach the max cursor issue - does it mean that unless I bounce / start / stop the application server I will not be ale to execute my query ? I understand if I dont close connections ultimately all connections in pool will get consumed and aplication will come to a standstill and we will be forced to restart application server

[akila]

Thanks Stephen - yes this part is understood regarding how to prevent this from happening - my question is more related to what is the impact if this happens - is this like leaked connections which can bring the application to a standstill - but in case of leaked cursors - will it bring the application to a standstill ?

[ziggy]

Yes correct because if you bounce the app server you are freeing up those resources.

[Stephen C]

@akila - leakage of ResultSet objects (i.e. failure to close them) will cause queries to fail, and that could bring an application to a standstill.

[akila]

thanks - I guess I will just try this out using some app server and confirm

[akila]

thanks Ziggy I will try this out using an application server and see what the impact is if I dont close result set and prepared statements

[Bagzli]

been looking for this answer for couple days now. What you have said is the first answer that made any sense. Thank you, you helped me debug my program.

[Esteban Cacavelos]

I'm glad this was usefull for you =)