ORA-01000 (max cursors exeeded) error

Posted on 2003-11-19
Medium Priority
Last Modified: 2008-02-07

In our VB .net application with oracle 9i we get the above error. We have used lot of ref cursors in our packages.

We thohght that ref cursor close automatically once the commit is issued.

Do I have to close ref cursor explicitly.

Can any one guide on above issues.

Question by:preyal_infosys
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
  • 4
  • 3
  • 3
  • +2
LVL 48

Expert Comment

ID: 9780217
One of the most common errors that occurs is an 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.
This error happens a lot in association with some kind of application, be it
 Oracle Office, OCI program, Pro*C program, Visual Basic, CDE Tools
(Reportwriter, Forms, etc.), Financial Applications, etc.
This error also happens at the database level, with just regular inserts,
updates, deletes, etc. in PL/SQL or in SQL*Plus, etc.  
The reason you receive this error is because Oracle has reached the set limit
for open cursors allowed for that executable or that user session. There are
two kinds of open cursors: implicit and explicit. Here is some background on
how cursors work.
To process a SQL statement, Oracle opens a work area called a private SQL area.
This private SQL area stores information needed to execute a SQL statement.
Cursors are stored in this area to keep track of information. An IMPLICIT
cursor is declared for all data definition and data manipulation statements.
These are internal to Oracle. For queries that return more than one row, you
must declare an EXPLICIT cursor to retrieve all the information. You can tune
explicit cursors more easily as you can decide when to open them and close them.
Implicit cursors are harder to tune because they are internal to Oracle. If your
application is tuned carefully, it may cut down the number of implicit cursors
Here is an example of how implicit cursors work and how you may get the
Bug 284745: receiving ORA-01000 on inserting no rows into a table with triggers.
The reason they are receiving ORA-01000 errors is because the triggers were
causing infinite recursive calls opening implicit cursors. For more details
please refer to bug 284745 and bug 237091.
Search Words:



Solution Description:
There are two ways to workaround this ORA-01000 error. You can tune cursor
usage at the database level and at the application level.
1. Tuning at the DATABASE LEVEL
There is a parameter you can set in the init.ora that determines the number of
cursors a user can open in a session: OPEN_CURSORS.  
OPEN_CURSORS by default is 50 and usually, this is not high enough. The highest
value you can set this parameter to is operating system dependant. For more
information, please refer to Oracle7 Server Administrator's Guide, Appendix A.  
To solve the ORA-01000 error, set the OPEN_CURSORS to a higher number (such as
255). You may need to set it to the maximum of the operating system limit.
Consequences to changing this parameter:

This parameter does not effect performance in any way but Oracle will now need
a little more memory to store the cursors.

2. Tuning at the APPLICATION LEVEL
There are three parameters that affect handling cursors at the application
parameters at the precompiler level.  
HOLD_CURSOR by default is NO. This means that after Oracle executes a SQL
statement the links to the cursor cache, memory, and parse locks are released
and marked for reuse.  For more details refer to Programmer's Guide to
Precompilers Version 1.6 p.6-16.
RELEASE_CURSOR by default is NO. This means that after Oracle executes a SQL
statement, the links to the cursor cache is maintained and not released. For
more information, refer to Programmer's Guide to Precompilers Version 1.6
These two parameters must be used in conjunction for them to be effective.
Here is a table that shows how settings of the two parameters interact.
      | NO           | not applicable | marked as reusable |
      | YES           | NO            | maintained         |
      | NO           | YES             | removed immediately|
      | n/a           | YES            | removed immediately|
To resolve the ORA-01000 error, you should set HOLD_CURSOR=NO and
RELEASE_CURSOR=YES. This way, after the cursors are used, Oracle will free up
the memory for other cursors.  
Consequences of setting these parameters HOLD_CURSOR=NO and RELEASE_CURSOR=YES:

This will cause Oracle to release the links and locks for each cursor after the
SQL statement is executed. This means that the next time Oracle needs to issue
the same SQL statement, Oracle will have to reparse the statement, and rebuild
the execution plan. This will cause some performance overhead.
MAXOPENCURSORS by default is 10. This number indicates the concurrent number
of open cursors that the precompiler tries to keep cached. It specifies the
initial size of the cursor cache. The limit of this parameter is determined by
what you set OPEN_CURSORS to. Here is the formula:

               6 is the overhead cursors Oracle needs.
Here is a calculation of the maximum number of cursors in use:
      SQL statement cursors
      PL/SQL parent cursors
      PL/SQL child cursors
       +6 cursors for overhead
      sum of cursors in use.
For more information, please refer to Programmer's Guide to the Oracle
Precompiler Version 1.6
LVL 23

Accepted Solution

seazodiac earned 750 total points
ID: 9780249
this is one of areas that Oracle still need to improve upon.
the fact of matter is that Cursors opened in your applications will persist even though your application explicitly close them. You can check this behaviors by v$open_cursor, and enterprise manager tool.

to ensure that oracle database server force to close the cursors and limit the number of cursors per session, you have to specify the following parameters in the init.ora file:

session_cached_cursors = <a small value>
cursor_space_for_time = false

you can combine these two with

open_cursors = <a higher value>

to avoid max cursors exeeded.

BTW, you do need to bounce the database in order for that change to take effec.t

and then try your vb app again after restarting the database

Expert Comment

ID: 9785286
This one appears on here quite a few times!  Here's a link to a previous question with lots of good answers:



Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.


Expert Comment

ID: 9785292
And yes you do need to close the cursor explicitly, but I believe all of the resources are released when the connection is dropped though, but not just on a commit (as you may still want to be working with the data).

Expert Comment

ID: 9787853
Grim_toaster is right. You have to close it yourself. And this behaviour is logical.
Using ref cursors is something different then using implicit cursors. You split the complete open-fetch-close sequence over twee (or more) applications. You tell the server to open a cursoraddress an give you that address
In you application you fetch from the cursor. When you commit, the cursor isn't closed by the database. Heck, mayby
you wan't to do a fetch-over-commit; how would he know! The application is responsible for fetching and closing, the server just opens the cursoraddress and associate it with the proper SQL statement.

Author Comment

ID: 9788614
thanks all for the answer.

But can any one guide me on how to close a ref cursor. Because I was under the impression that ref cursors are closed on commit.

You donot have to close them explicity.

LVL 23

Expert Comment

ID: 9788654
You are right about this, you don't need to close the REF CURSOR. they will automatically close upon your exit or commit.

I will emphasize the following approach since it proves very effective for multiple occasions:

to ensure that oracle database server force to close the cursors and limit the number of cursors per session, you have to specify the following parameters in the init.ora file:

session_cached_cursors = <a small value>
cursor_space_for_time = false

you can combine these two with

open_cursors = <a higher value>

Author Comment

ID: 9788820
Also i want to know the steps to change values for RELEASE_CURSOR, HOLD_CURSOR, MAXOPENCURSORS

Can any one help me on this
LVL 23

Expert Comment

ID: 9788991
luckily, In oracle9i, these parameters have been phased out. you don't need to set them up any longer.

Author Comment

ID: 9789216
I have

session_cached_cursors =  0
cursor_space_for_time = false
open_cursors =  300

But still I get that error. Can you guide me on this. Now what should I do.

Because the new application genearating this error is on production server. Any we cannot afford production server to go down frequently.

How should I  modify my ref cursor, so that they get closed.

Can we make any changes in our front end (.Net) application to solve this prob

LVL 23

Expert Comment

ID: 9789373
try to set

session_cached_cursors =  60

Expert Comment

ID: 9795284
You should close your ref cursor when finished its use, the link below gives examples of dealing with and closing Oracle ref cursors, under the section "Oracle REF CURSORs".  Code is provided for VB (.net I presume?) and c#.

LVL 48

Expert Comment

ID: 9795531
The open cursor management is an issue.

I think the following is for 8i, but may be it exists also in 9i.

Add this line to you init file and bounce your systems.


The parameter is now hidden and you need to use the underscore as:


Generally support has advised the consideration of the parameter if the application does not regularly close cursors as they will remain in the library cache until they are closed. The side effect is that you may close more cursors than you wish to, such as in the situation of nested cursors

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.

649 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