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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

627 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