ORA-01000: maximum open cursors exceeded tips

Oracle docs note this about ORA-01000:


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.

Question:

I keep encountering ORA-01000 and can't even create a table

Answer:

First, you should try increasing your OPEN_CURSORS and take a look at the application to see if/why cursors are staying open. Here is an example from a forum:

ALTER SYSTEM SET open_cursors = 400 SCOPE=BOTH;

Furthermore, to resolve ORA-01000, try to close whatever cursors are no longer in use, raise the OPEN_CURSORS parameter within your initialization file, and restart Oracle.

MOSC also offers information regarding ORA-01000 when it occurs with inserts in PL/SQL or SQL*Plus. ORA-01000 is thrown because of a maxed limit of open cursors in an execution or user session. Working around ORA-01000 can be done on the database level by setting the init.ora parameter, OPEN_CURSORS, which states the maximum cursors allowed. Note that the OPEN_CURSORS are defaulted at 50 which may not be high enough, causing ORA-01000, and that each operating system determines the appropriate value.

Resolving ORA-01000 try setting this OPEN_CURSORS to a higher number. Keep in mind when resolving ORA-01000 in this way that changing this value in the proper way should not alter the system performance, but will probably require more memory for cursor storing.

Thanks - http://www.dba-oracle.com/sf_ora_01000_maximum_open_cursors_exceeded.htm