Sunday, April 20, 2008

ORA-01000:maximum open cursors exceeded

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.

ORA-01000: "maximum open cursors exceeded":

This error occurs when the number of cursors currently open by a single database connection have exceeded the value of the "open_cursors" paramerer in the "init.ora" file.

You can find the current maxiumum value allowed using "show parameter open_cursors" from SQL*Plus or Server Manager.

This is a per user limit, and is the number of cursors which have been opened but not closed since the database connection was made. A large limit is typically required when performing a large number of queries since the last commit, such as validating a catalogue. The cause is usually "recursive SQL", e.g. having to extend a table or firing of triggers.

There may be a bug somewhere in the application so that a series of cursors are being opened and not closed.

As a workaround, the user could log out and log back in again, going straight to the function which caused the error. If this resolves the problem, it is likely that cursors were remaining open from a previous function.

No comments: