Oracle Error: ORA-01000 Maximum open cursors exceeded

Technote (FAQ)
Problem
How to avoid the ORA-01000 be prevented?
Solution
Typically, in Java, when an object goes out of scope, it is automatically garbage collected, but the Java specification does not place any specific requirements on the JVM regarding when (or even if) this will occur for any particular object. Therefore, do not rely on finalizers to close cursors. A finalizer is called when a class is garbage collected.

Explicitly close all cursors (e.g. ResultSet.close() and Statement.close()) when the cursors are no longer needed. This ensures that the corresponding server-side cursors are closed, preventing the ORA-01000 error.

The SQL_TEXT column can be queried in V$OPEN_CURSOR view to determine which cursors are not being closed. For example:

select sql_text from v$open_cursor;

Notes:
1. Typically, close() statements should be put in a finally clause of the try block.
2. There are known problems in certain versions of Oracle 8.1.6 JDBC drivers that can make this problem worse. The Oracle 8.1.7 JDBC driver or a later version should be used.











Document Information

Product categories: Software, Application Servers, Distributed Application & Web Servers, WebSphere Application Server, EJB Container
Operating system(s): All Platforms
Software version: 3.5, 4.0, 5.0, 5.1, 6.0
Software edition: Edition Independent
Reference #: 1040875
IBM Group: Software Group
Modified date: 2003-06-20