ORA-01000 (max cursors exeeded) error

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
opened.
 
Here is an example of how implicit cursors work and how you may get the
ORA-01000:
 
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:
=============

ORA-1000

Solution: WORKAROUNDS FOR ORA-01000

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
level: RELEASE_CURSOR, HOLD_CURSOR, MAXOPENCURSORS. You should set these
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
p.6-26.
 
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.
 
        ----------------------------------------------------
      |HOLD_CURSOR | RELEASE_CURSOR | LINKS ARE...       |
      ----------------------------------------------------
      | 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:

      MAXOPENCURSORS + 6 <= OPEN_CURSORS
               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 one appears on here quite a few times!  Here's a link to a previous question with lots of good answers:

https://www.experts-exchange.com/questions/20784012/MAX-NUMBER-OF-CURSORS-EXCEEDED-ERROR.html

and

http://www-level3.experts-exchange.com/questions/20743275/MAX-OPEN-CURSORS-workarounds.html

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.

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>

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.

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

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp

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.

_close_cached_open_cursors=true

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

_close_cached_open_cursors=true


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