How to Resolve ORA-01000: maximum open cursors exceeded

  • by

ORA-01000

Saw an error ORA-01000: maximum open cursors exceeded when executing a lot of statements. It seemed that the number of open cursors has reached the limitation.

ORA-01000: maximum open cursors exceeded

The error relates to the limitation of OPEN_CURSORS initialization parameter. Let's see its current setting.

SQL> show parameter open_cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     300

The number 300 is the default value of OPEN_CURSORS. Let's raise the number of open cursors that we can use.

SQL> alter system set open_cursors=30000 scope=both;

System altered.

Eventually, I added not one but two digits to OPEN_CURSORS to make my job done.

To reduce the use of open cursors, I think you might like to know how to use bind variables in PL/SQL.

Leave a Reply

Your email address will not be published. Required fields are marked *