LinkedIn
Copied!

Table of Contents

Troubleshooting "Too many open cursors" (Oracle)

Summary

For an Oracle database, the OPEN_CURSORS parameter is an initialization parameter which defines the maximum number of cursors per user.  As a best practice, set this parameter to at least 300.


 

Suggested Approach

The OPEN_CURSORS parameter is an initialization parameter which defines the maximum number of cursors per user.  There are two types of cursors, Implicit and Explicit.  Implicit cursors are defined by the Oracle system as Process Commander requires them; they are out of the control of the executing application.  Explicit cursors are specifically defined by the application when constructing SQL Statements that will return more than a single row.

By default, Oracle assigns a value of 50 to this parameter.  This number is too low to run Process Commander, and will produce Oracle errors stating "Too Many Open Cursors."

It is possible to use the sample init.ora file to set runtime parameters for an Oracle database; in this file, OPEN_CURSORS is set to 300.

Per Oracle's documentation:

 "It is important to set the value of OPEN_CURSORS high enough to prevent your application from running out of open cursors.  The number will vary from one application to another.  Assuming that a session does not open the number of cursors specified by OPEN_CURSORS, there is no added overhead to setting this value higher than actually needed."

Based on the above information, as a best practice, set this parameter to at least 300.  (The above Oracle quote is from the Oracle 8i Initialization Parameters documentation for the parameter OPEN_CURSORS.)

Suggest Edit

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.