File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Maximum Open Cursors exceeded... URGENT!!! Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Maximum Open Cursors exceeded... URGENT!!!" Watch "Maximum Open Cursors exceeded... URGENT!!!" New topic
Author

Maximum Open Cursors exceeded... URGENT!!!

Chhaya Dhanani
Ranch Hand

Joined: Apr 11, 2001
Posts: 70
hi all,
i have a connection pool which is allocating 10 connections in my classes..
i have used only one connection per session.. and taken care of returning it back to the pool.This is logged in a log file which lets me know which class has not returned a connection..
the problem i am facing is although connections are closed , i am getting a maximum cursors exceeded exception on oracle..
i have taken care to close all resultsets and statements..
Why is this still happening.. can any one suggest a way out
is it possible to find out which class is not closing the statements and resultsets by writing a small java utility code. or any other work around...(as i have around 80 classes and going thru each one of them will not be possible)
PLEASE REPLY ASAP!!!
Thanx in advance..
Chhaya
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

Can we get some details on your code?
Can you read this similar question (i got from a Google search) and see if it is similar?
Dave.
Geoff Gomez
Greenhorn

Joined: Dec 20, 2001
Posts: 2
Originally posted by Chhaya Dhanani:
hi all,
i have a connection pool which is allocating 10 connections in my classes..
i have used only one connection per session.. and taken care of returning it back to the pool.This is logged in a log file which lets me know which class has not returned a connection..
the problem i am facing is although connections are closed , i am getting a maximum cursors exceeded exception on oracle..
i have taken care to close all resultsets and statements..
Why is this still happening.. can any one suggest a way out
is it possible to find out which class is not closing the statements and resultsets by writing a small java utility code. or any other work around...(as i have around 80 classes and going thru each one of them will not be possible)
PLEASE REPLY ASAP!!!
Thanx in advance..
Chhaya
Geoff Gomez
Greenhorn

Joined: Dec 20, 2001
Posts: 2
This isn't really to do with your code, it is caused by having too many statements open within a single session. To cure the problem you might want to (get your DBA to) increase the value of "open_cursors" in your init.ora file. Alternatively you might want to reduce the ammount of stuff being done by each connection (maybe by adding more).
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Chhaya:
double check and triple check that all statements and resultsets are CLOSED before the connection is returned. If the problem still persists and you are positive that all statements are closed on the connection, then when you receive the connection back to the pool, perform a Connection.rollback() to free all the DBMS resources if for some reason some are lingering.
Jamie
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

I'm concerned when you say "i have used only one connection per session".
If you are storing Connections (ie Database resources) on the session you are probably asking for trouble.
I agree with Jamie, but I'd go further and say it might be worthwhile re-architecting your resource management.
Dave
Kenneth Reising
Greenhorn

Joined: Jul 30, 2001
Posts: 10
Make sure you are also handling Exceptions and overriding Finalize() to properly return the connections to the pool, and closing resultsets, statements, and the like.
Also, as pointed out above:
"i have used only one connection per session".
means you can only have as many sessions as you do connections in the pool.
And of course, make sure that Oracle does in fact have enough free cursors (connections) that it can properly handle your connection pool size. (if oracle only has 5 available connections, no way you are going to get 10 connections out of it).

HTH;
Chhaya Dhanani
Ranch Hand

Joined: Apr 11, 2001
Posts: 70
Thanx david, geoff, jamie for all your help.
i am going to try connection.rollback() and see if it helps me solve my problem.
and once aggain double check the code for any open statments and resultsets...(though i am sure their are'nt any!!)
Thanx once again..
Chhaya
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

alright, if you are positive that all statements/resultsets are closed, here is the skinny:
Oracle has some internal problems with prepared statements. On many occasions, even though you close the resultset and prepared statements, the cursors remain open. Soon you will get "Ora-1000 max number of cursors exceeded" or something to that effect. A service call in to oracle seemed to imply that there were some bugs in their jdbc implementations - any Statement you created would internally spawn cursors in the db (from their drivers) that weren't getting closed. apparently this has been fixed in the 8.1.7 release, classes12.zip. I still found that we needed to set our open_cursors (in init.ora, on the db server) to a fairly high number (100's).
There are 3 workarounds to help you:
1. set max number of open cursors in Oracle to a high number (at least 200... the default is 40ish) as this does not effect performance of the DB.
2. force a rollback: connection.rollback()
3. close the connection: connection.close()
hope this helps
Some of the above had already been stated, but I thought I would try to be as thorough as possible. I have dealt with this problem and it is frustrating. I usually don't post this until I'm sure that the person with the problem spends time ensuring that all resultsets/statements are closed(which is the problem 99% of the time). Make sure that you are using the latest classes12.zip version 8.1.7(backward compatible).
Jamie
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

after my little rant, I thought of one last possibility that seems to slip by everyone. Do you have a method that returns a resultset any where? eg

do you have any of these types of methods that return a resultset?
Jamie
simplex du
Greenhorn

Joined: Mar 25, 2002
Posts: 24
Jamie,thank you!
 
wood burning stoves
 
subject: Maximum Open Cursors exceeded... URGENT!!!