• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Maximum Open Cursors exceeded... URGENT!!!

 
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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).
 
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 70
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Jamie,thank you!
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic