Maximum open cursors have been exceeded

Problem

During large imports on Oracle, you could receive following error message:

ORA-01000: maximum open cursors exceeded

(The cursors are used only during the import; they then are closed.)
 

Issue the following sql*plus utility command to check the current value for maximum open cursors:

show parameter open_cursors

A listing similar to the following will display:

SQL> show parameter open_cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     1000

Solution

An open_cursors value of 1000 should be sufficient for all large imports.

You can temporarily set the open_cursors value with the following SQL:

alter system set open_cursors=1000

To make a permanent change, you must set the open_cursors value in the initialization parameters file.