Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

Support Article

ORA-01000 Maximum open cursors exceeded (after HFix-29955)

SA-41181

Summary



After installing HFix-29955, which fixes the error ORA-01000: Maximum number of open cursors exceeded, the error persists.


Error Messages



DatabaseException caused by prior exception: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
2017-06-20 15:25:30,796 INFO [stdout] (Dispatcher-Thread-99) ORA-01000: maximum open cursors exceeded


Steps to Reproduce


Unknown

Root Cause



A defect or configuration issue in the operating environment

The default value for Oracle is 300. This is not sufficient for the Pega Platform.

Resolution



Make the following change to the operating environment:

Increase the value of the Oracle database parameter open_cursors as explained in the Oracle Help Center and the Oracle Online Documenation.
  1. Go to the Oracle Help Center for Middleware, the Oracle Fusion Middleware Online Documentation Library, 11g Release 2 (11.1.2.2).
  2. In the Oracle Fusion Middleware Administrator's Guide for Oracle Entitlements Server (OES), go to Chapter 16, Tuning Performance and Monitoring Components, Section 16.3.1, Tuning the OES Policy Store, Subsection 16.3.1.1 Oracle Database System Parameters Tuning.
  3. After Table 16-1, Oracle Database System Parameters Tuning (where the open_cursors parameter is defined), see the SQL script for setting the system parameters, which includes the following statement:
    ALTER SYSTEM SET open_cursors = 500 SCOPE = spfile;
    The SQL script shown must be run as a DB user with the SYSDBA role.
  4. See also the Oracle Database Online Documentation for 12c Release 1 (12.1) Database Administration, Section 1.178 OPEN_CURSORS.
The number of open cursors is in direct relationship to the number of open connections being made to the database. Setting this database value ensures that you do not exceed it in any given process, such as falling into a infinite loop or a process not properly closing connections when it should be closing them.

With that said, 300 open cursors can inevitably fall victim to the ORA-01000: maximum open cursors exceeded error in the logs and cause other Pega Platform issues. To put this into perspective, an internal system at Pega has its Development, Test-QA, and Staging environments with this Oracle database parameter set to 2000, and its Production environment set to 3000. This open cursors setting is also proportionate to the number of users, the size of the application, service calls, report queries, and all of the other features of the application. The default value of 300 is exceptionally low.
Suggest Edit

Published August 29, 2017 - Updated October 8, 2020

Did you find this content helpful? Yes No

100% found this useful

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us