Skip to content

How to Resolve ORA-01000: maximum open cursors exceeded


Saw an error ORA-01000 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 only one but two digits to OPEN_CURSORS to solve the error and make my job done, furthermore, OPEN_CURSORS can be modified at run-time and in SPFILE.

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 *