?
Solved

ora-01000 Maximum open cursor exceeded

Posted on 2003-03-13
8
Medium Priority
?
3,683 Views
Last Modified: 2008-02-20
Hi,

I'm facing problem with my OCI program. It says 'Maximum open cursor exceeded'. I am calling OCIHandleFree for every statement handle I used, but still number of cursors increases. Does anyone know how to close the cursor using OCI call?

Thanks in advance,
Savdekar
0
Comment
Question by:savdekar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 8

Expert Comment

by:heskyttberg
ID: 8127389
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
0
 
LVL 5

Expert Comment

by:DrJekyll
ID: 8127933
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);    }
0
 
LVL 8

Expert Comment

by:Danielzt
ID: 8128188
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.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 48

Expert Comment

by:schwertner
ID: 8128337
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.
0
 
LVL 3

Expert Comment

by:patelgokul
ID: 8135687
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.


0
 

Author Comment

by:savdekar
ID: 8523418
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
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 10094054
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
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 10148235
PAQed, with points refunded (125)

Computer101
E-E Admin
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question