Setting Maximum Open Cursors In Oracle

This article talks about how to set maximum open cursors in Oracle. When the open cursors exceeds the maximum limit you will get the below error:

ORA-01000: maximum open cursors exceeded!

Cause:

A host language program attempted to open too many cursors. The initialization parameter OPEN_CURSORS determines the maximum number of cursors per user.

Action:

Modify the program to use fewer cursors. If this error occurs often, shut down Oracle, increase the value of OPEN_CURSORS and then restart Oracle.

How?
You can use any of the following options to set maximum open cursors:

ALTER SYSTEM SET OPEN_CURSORS=2000 SCOPE=MEMORY;
or
ALTER SYSTEM SET OPEN_CURSORS=2000 SCOPE=SPFILE;
or
ALTER SYSTEM SET OPEN_CURSORS=2000 SCOPE=BOTH;
or
you can set this parameter value at instance or database level.

ALTER SYSTEM SET OPEN_CURSORS=2000 SID='*' SCOPE=BOTH;

OPEN_CURSORS are defaulted at 50 which may not be high enough for your applications, causing ORA-01000 and that each operating system determines the appropriate value.

1 Comments

Did you enjoy this post? Why not leave a comment below and continue the conversation, or subscribe to our feed and get articles like this delivered automatically to your feed reader? Like our Facebook Page.

  1. Awesome post. I also faced this issue recently but then I just modified the program to use fewer cursors. I was not aware about the reason behind this problem but after reading this article I came to know about it. Thanks for sharing so many options to set the maximum open cursors.
    sap testing tools

    ReplyDelete
Post a Comment
Previous Post Next Post