Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

ora-01000 Maximum open cursor exceeded

Posted on 2003-03-13
8
Medium Priority
?
3,695 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
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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 …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

564 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