Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1775
  • Last Modified:

MAX NUMBER OF CURSORS EXCEEDED ERROR

Hello,

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.

CREATE OR REPLACE FUNCTION sp_get_stocks(v_price IN NUMBER)
    RETURN Types.ref_cursor
AS
    stock_cursor types.ref_cursor;
BEGIN
    OPEN stock_cursor FOR
          SELECT ric,price,updated FROM stock_prices
          WHERE price < v_price;
    RETURN stock_cursor;
END;
/

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,
Balaji
0
classical74
Asked:
classical74
4 Solutions
 
grim_toasterCommented:

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
ORDER BY COUNT(sql_text) DESC

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!)

i.e.

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

if (stmt != null {
try {
stmt.close();
} catch (final Exception e) {
...
}
0
 
schwertnerCommented:
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.

_close_cached_open_cursors=true

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

_close_cached_open_cursors=true


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
0
 
DanielztCommented:

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

http://otn.oracle.com/sample_code/tech/pl_sql/htdocs/x/Cursors/start.htm
0
 
seazodiacCommented:
Can you check your init<SID>.ora file for these parameters?

SESSION_CACHED_CURSORS
CURSOR_SPACE_FOR_TIME


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

SESSION_CACHED_CURSORS = 0
CURSOR_SPACE_FOR_TIME =FALSE

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.
http://www-level3.experts-exchange.com/Databases/Oracle/Q_20743275.html

Another solution is to use parameterized Cursor.
Instead of opening new cursors every loop, you reopen the cursor with the different parameters.
0
 
classical74Author Commented:
Many thanks for your inputs.I'll update you on the results soon.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now