Solved

MAX NUMBER OF CURSORS EXCEEDED ERROR

Posted on 2003-10-31
7
1,752 Views
Last Modified: 2007-12-19
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
Comment
Question by:classical74
7 Comments
 
LVL 7

Accepted Solution

by:
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
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
 
LVL 47

Assisted Solution

by:schwertner
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.

_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
 
LVL 8

Assisted Solution

by:Danielzt
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.

http://otn.oracle.com/sample_code/tech/pl_sql/htdocs/x/Cursors/start.htm
0
 
LVL 23

Assisted Solution

by:seazodiac
seazodiac earned 23 total points
ID: 9659003
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
 

Author Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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 …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now