Posted on 2003-10-31
Last Modified: 2007-12-19

There is one error which comes occasionally in our oracle database, but a hazaourdous and that is --> Maximum Open Cursors exceeded.

There are some explicit cursors used in our stored procedures. A procedure is simulated with an example below.

    RETURN Types.ref_cursor
    stock_cursor types.ref_cursor;
    OPEN stock_cursor FOR
          SELECT ric,price,updated FROM stock_prices
          WHERE price < v_price;
    RETURN stock_cursor;

The procedure is called by a Java program (thru JDBC) and the prepared statement is also closed at the end of the program in the finally block. I guess if we close the prepared statement then it will make sure every explicit cursor will be closed. But I think it is not happening.

When ever I try to query the no of cursors, it returns me more than 200 open cursors at any point of time. The max no of cursors set is = 300 FYI.

Please let me know how to close the open cursors.  Because I see that it cannot be closed inside the function as it returns the result set (cursor reference).

It would be great if anyone shall help to solve this problem asap as it is kind of a workstopper.

FYI..the functions are in a package.

Regards and Wish you have a good day,
Question by:classical74
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

grim_toaster earned 26 total points
ID: 9657152

Could you post a sample of your Java code?

You state that the prepared statement is closed at the end of the program, do you mean at the end of each call?  Although the Statement interface (which CallableStatement and PreparedStatement implement) declares that when you close the statement it should clear all of the resources, I've come across a couple of drivers that don't (but thankfully these are getting rarer!).

Are you sure that it is this query that is causing the problems?  

You can use the query:

SELECT sql_text, COUNT(sql_text)
FROM   v$open_cursor
GROUP BY sql_text

to find the most open cursors (if that makes sense!?), but it is not always an exact answer!

Also, I would normally recommend closing each item explicitly, the ResultSet's, etc (I know this may cause some outcry!), it's the old belt and braces aproach!  Each within its own try/catch block with checks for null.

(Note that I catch Exception instead of SQLException, as I've had problems when dealing with LOB's throwing NullPointerExceptions where the code was doing things it shouldn't and thereby not closing resources correctly!)


if (rset != null) {
try {
} catch (final Exception e) {

if (stmt != null {
try {
} catch (final Exception e) {
LVL 48

Assisted Solution

schwertner earned 23 total points
ID: 9657317
1. You should not use the finally block to reliably close cursors.
2. You should design your code to ensure that the resultSet/cursor and staements go out of scope after the close() method is invoked. This is necessary in order to execute garbage collection and reclaim the memory allocated for cursors

Turned out that you must close the statement as well
as the result set. Closing the statement only or the
result set only was not sufficient.

If you receive messages that you are running out of cursors or that you are running out of memory, make sure that all your Statement and ResultSet objects are explicitly closed. The Oracle JDBC drivers do not have finalizer methods. They perform cleanup routines by using the close() method of the ResultSet and Statement classes. If you do not explicitly close your result set and statement objects, significant memory leaks can occur. You could also run out of cursors in the database. Closing a result set or statement releases the corresponding cursor in the database.

Similarly, you must explicitly close Connection objects to avoid leaking and running out of cursors on the server side. When you close the connection, the JDBC driver closes any open statement objects associated with it, thus releasing the cursor objects on the server side.

Add this line to you init file and bounce your systems.


The parameter is now hidden and you need to use the underscore as:


Generally support has advised the consideration of the parameter if the application does not regularly close cursors as they will remain in the library cache until they are closed. The side effect is that you may close more cursors than you wish to, such as in the situation of nested cursors

Assisted Solution

Danielzt earned 23 total points
ID: 9658013

here is good paper for Oracle cursor.
if you want to understand more trick, try to read it.
LVL 23

Assisted Solution

seazodiac earned 23 total points
ID: 9659003
Can you check your init<SID>.ora file for these parameters?


in order to avoid the maximum open cursor exceeds error, you should have these two parameters set to:


And restart the database , run your application and see if this helps.

This solution worked for other users., take a look at this link, and I have prodived this solution.

Another solution is to use parameterized Cursor.
Instead of opening new cursors every loop, you reopen the cursor with the different parameters.

Author Comment

ID: 9691892
Many thanks for your inputs.I'll update you on the results soon.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question