AK <org> wrote
Default value is 50 open cursors. It is set via OPEN_CURSORS (pfile or
spfile or ALTER SYSTEM).
From the Oracle Reference Manual:
OPEN_CURSORS specifies the maximum number of open cursors (handles to
private SQL areas) a session can have at once. You can use this
parameter to prevent a session from opening an excessive number of
cursors. This parameter also constrains the size of the PL/SQL cursor
cache which PL/SQL uses to avoid having to rep as statements are
reexecuted by a user.
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
Set it higher. Also keep an eye on potential cursor leakage - i.e.
PL/SQL or Pro*C or other client code that opens cursors and never
close them when done.
E.g. returning a ref cursor from PL/SQL to Pro*C and then doing a
close on that refcursor pointer in Pro*C does not close the ref cursor
in Oracle. Unsure if this is a Pro*C bug, but saw it happen on a 3
tier (Corba) system. The fix I proposed in that case was to call a
PL/SQL proc with the refcursor variable in order to close it in Oracle
after the Pro*C code was done with the refcursor - it worked thus far.