wood burning stoves 2.0*
The moose likes JDBC and the fly likes ORA-01000: maximum open cursors exceeded Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "ORA-01000: maximum open cursors exceeded" Watch "ORA-01000: maximum open cursors exceeded" New topic
Author

ORA-01000: maximum open cursors exceeded

Venita Glasfurd
Greenhorn

Joined: Sep 11, 2002
Posts: 12
Please help as am facing the following problem with an Oracle database.
A Connection to the database is opened and PreparedStatements are created to insert into the database. As there is an idle timeout defined on the database, the Connection is lost and I need to reconnect to the database.
The code I have goes like this:-

In spite of closing the PreparedStatements, the application throws an error "ORA-01000: maximum open cursors exceeded", when the connection is lost and we try to recreate the PreparedStatements.
Any pointers to fix this will be appreciated.
Thanks,
Venita.
[ March 03, 2003: Message edited by: Venita Glasfurd ]
Leslie Chaim
Ranch Hand

Joined: May 22, 2002
Posts: 336
I would first try to also clear() your PreparedStatements for your List and put in a call to System.gc()


Normal is in the eye of the beholder
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

Hi Venita,
When using the Oracle database and the Oracle JDBC (thin) driver, you must close "ResultSet" objects _before_ closing "PreparedStatement" objects. From the code you have posted, it seems that you are closing the "PreparedStatement"s first, and then the "ResultSet" -- which raises the question, 'why is there a collection of "PreparedStatements" but only one "ResultSet"?'
A "ResultSet" object is associated with exactly one "[Callable/Prepared]Statement". Looks like your code may be "leaking" Oracle cursors in any event (regardless of whether you have lost the database connection, or not).
In my experience, using Oracle 8.1.7.4 database with the Oracle (thin) JDBC driver on SUN [sparc] Solaris 7 and J2SE version 1.3.1, if you close the "PreparedStatement" before (or without) closing the "ResultSet", the (Oracle) cursors remain open!
I think you may need to refactor your code!
Hope this has helped you.
Good Luck,
Avi.
Kirtikumar Puthran
Ranch Hand

Joined: Mar 04, 2003
Posts: 37
Hi Venita,
Try closing the prepared statement. In JDBC, preparedStatement.close() closes the cursor.
Still, if the problem persists, change the initialization parameter OPEN_CURSORS, restart the instance and have a look. You can query the v$parameter to have a look at the number of open cusrors.

Hope this helps.
Rgds,
Kirtikumar Puthran


Regards,<br />Kirti
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
Closing a statement should close the ResultSet(s) associated with it. However, especially with the Oracle driver you must close your statements with the utmost discipline. And if that close() statement is not in a finally clause, you have a resource leak on your hands!
If you're using connection pooling as well, this gets even worse -- if you have a leak somewhere, no matter how small, you will get the ORA-1000 problem.
- Peter
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

Kirtikumar and Peter,
What version of the Oracle database and JDBC driver and JRE, together, demonstrates the behaviour you describe?
I have verified the behaviour I have described (in my environment -- which I detailed in my previous reply).
A co-worker has informed me that there is a technical note on Oracle's "MetaLink" Web site:
http://metalink.oracle.com
that also verifies the behaviour I am seeing.
PLEASE tell me what combination I need, in order to get the behaviour you are describing!
Thanks (in advance :-),
Avi.
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
Avi,
The fact that closing a Statement closes its ResultSet (or, in JDBC 3.0, ResultSets) is documented in the JDBC API javadoc and in the JDBC specification (section 13.1.3 for v3.0). Without this, a driver will never get through Sun's JDBC test suite or J2EE certification.
My experience is that this mandated behaviour is implemented correctly in the Oracle JDBC driver -- the most telling example is a JDK 1.3.1 web project over an Oracle 8.1.7 EE database I did a few months ago (using release 2 of the thin driver); the code completely relies on this behaviour. Had been any cursor leak I would've noticed as the thing has been scoped and load tested for 30M hits/month. At such sustained loads the smallest leak will cause everything to collapse in a big heap sooner rather than later (obviously, not every page hit will hit the database but it's enough to keep a 4-CPU/4GB box nicely occupied).
- Peter
[ March 06, 2003: Message edited by: Peter den Haan ]
Kirtikumar Puthran
Ranch Hand

Joined: Mar 04, 2003
Posts: 37
Hi Avi,
In my post, I am referring to Oracle 8i, JRE 1.3.0 and JDBC 1.3.
I fully agree with Peter when he says:
However, especially with the Oracle driver you must close your statements with the utmost discipline. And if that close() statement is not in a finally clause, you have a resource leak on your hands!

We had a tough time on our previous project with "OPEN_CURSORS", which was developed using Oracle 8i, jdk 1.2.2, and JDBC 1.2 API. The problem was ultimately solved to a great extent when we closed the Statement and the Connection objects in the finally clause of all our servlets.
Rgds,
Kirtikumar Puthran
SJ Adnams
Ranch Hand

Joined: Sep 28, 2001
Posts: 925
is anything else using the database? I had this problem last week & after looking at V$OPEN_CURSOR there were only 200 or so open (10 from my application, i.e. the connection pool). So I assumed someone elses job had eaten them all up.
what does,
select sql_text, count(sql_text) from v$open_cursor group by sql_text;
tell you?
Kirtikumar Puthran
Ranch Hand

Joined: Mar 04, 2003
Posts: 37
select sql_text, count(sql_text) count from v$open_cursor group by sql_text order by count;
The above query will return or show you all the open cursors.
Rgds,
Kirtikumar Puthran
Dave Hudach
Greenhorn

Joined: Feb 07, 2004
Posts: 2
I am running into this at the present time. I am closing resultset, preparedstatement and connection in my finally block. I am processing a spreadsheet, and for each row doing some DB lookups. No big deal, rather similar to a batch program. However, perhaps this is a 'best practices' question or issue. My queries are simply static methods in a class of queries required for processing. Each static method creates a connection, runs the query and closes the resources. This is a web app using connection pooling.

Any thoughts on using static methods this way?

Thanks,
Dave
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: ORA-01000: maximum open cursors exceeded
 
Similar Threads
ORA-01000: maximum open cursors exceeded
ORA-01000: maximum open cursors exceeded
ORA-01000: maximum open cursors exceeded
ORA-01000 maximum open cursors exceeded
error "ORA-01000: maximum open cursors exceeded"