ora-01000 Maximum open cursor exceeded

Hi!

I depends on what you are doing in the database, you ned to free the cursor and close the connection.

You can also increase the number of cursors allowed in Oracle. Since the cursors won't always releas at once.

The standard value for open cursors is 40 or something, which is quite low.

Regards
/Hans - Erik Skyttberg

You can try increase max_open_cursors to something like 500.
A guess on how to close cursor. If cursor area is defined
as cda

if (oclose&cda)) /*    close cursor */
  {fprintf(stderr, "Error closing cursor 1.\n");
   do_exit(OCI_EXIT_FAILURE);    }

you can ask your DBA to increase the open cursors.

OPEN_CURSORS specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once. You can use this parameter to prevent a session from opening an excessive number of cursors. This parameter also constrains the size of the PL/SQL cursor cache which PL/SQL uses to avoid having to reparse as statements are reexecuted by a user.

when you run your application, you can query V$open_cursor to know how many cursor your are using. The most important thing is to check you code, close all the cursor after your transaction.

Try to catch all exceptions generated in your program. force closing of open cursors. Very often this message means that the program meets exception and quiet closes, but do not closes the opened cursor.

try this


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.

modify the value of open_cursors (default 100) in init.ora
file.

Hi,

Sorry for late posting, but My problem get solved when I apply patch to oracle. FYI: Check out 'Bug 1607828' which is fixed in 8172.

Pankaj

No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

PAQ with points refunded

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

anand_2000v
EE Cleanup Volunteer