ORA-01000 (max cursors exeeded) error


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.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
This one appears on here quite a few times!  Here's a link to a previous question with lots of good answers:



Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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).
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.
preyal_infosysAuthor Commented:
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.

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>
preyal_infosysAuthor Commented:
Also i want to know the steps to change values for RELEASE_CURSOR, HOLD_CURSOR, MAXOPENCURSORS

Can any one help me on this
luckily, In oracle9i, these parameters have been phased out. you don't need to set them up any longer.
preyal_infosysAuthor Commented:
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

try to set

session_cached_cursors =  60
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#.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.