"ORA-01000: maximum open cursors exceeded" connecting to an Oracle database from WebSphere Application Server
 Technote (troubleshooting)
 
Problem(Abstract)
Applications running in WebSphere Application Server V5 or V6 may see the following error when connecting to an Oracle database:

ORA-01000: maximum open cursors exceeded
 
Cause
The cause of the problem is that the maximum number of open cursors that are available in Oracle has been exceeded. In Oracle, the maximum number of open cursors is set per connection. The problem will occur if there are too many Statements and ResultSets open on a connection that was obtained from a WebSphere Application Server data source. This could occur if:
  • The application fails to close Statement and ResultSet objects when it is finished using them
  • The Statement cache size property for the data source is set to a value higher than the maximum number of open cursors in Oracle
 
Resolving the problem
To resolve the problem, you should first review the application code to ensure that it is closing all JDBC Statement and ResultSet objects when it finishes using them. Secondly, you should compare the value of the Statement cache size for your data source to the maximum number of open cursors in Oracle.

To check the Statement cache size:

  • In WebSphere Application Server V5, navigate to the data source in the Admin Console. The Statement cache size appears on the main data source configuration panel.
  • In WebSphere Application Server V6, navigate to the data source in the Admin Console. Under Additional Properties, select WebSphere Application Server data source properties. The first property listed on the resulting screen is the Statement cache size.

To check the maximum number of open cursors, open the initSID.ora file, where SID is the Oracle system identifier for the database. The file can be found in the <Oracle home>/admin/SID/pfile directory. Look for the open_cursors setting.

Set the open_cursors in Oracle to a value larger than the Statement cache size for the data source in WebSphere Application Server. Also set the following property in the initSID.ora file:
cursor_sharing = force

These actions will ensure that the number of cursors opened by your application running in WebSphere Application Server will not exceed the maximum number of open cursors in Oracle.

 
 
Cross Reference information
Segment Product Component Platform Version Edition
Application Servers Runtimes for Java Technology Java SDK
 
 


Document Information


Product categories: Software > Application Servers > Distributed Application & Web Servers > WebSphere Application Server > DB Connections/Connection Pooling
Operating system(s): Windows
Software version: 6.0.2.2
Software edition:
Reference #: 1218727
IBM Group: Software Group
Modified date: Sep 30, 2005