Solved

ORA-01000 (max cursors exeeded) error

Posted on 2003-11-19
13
6,472 Views
Last Modified: 2008-02-07
Hi,

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.

Thanks
Preyal
0
Comment
Question by:preyal_infosys
  • 4
  • 3
  • 3
  • +2
13 Comments
 
LVL 47

Expert Comment

by:schwertner
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
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
 
0
 
LVL 23

Accepted Solution

by:
seazodiac earned 250 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
0
 
LVL 7

Expert Comment

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

http://www.experts-exchange.com/Databases/Oracle/Q_20784012.htm

and

http://www-level3.experts-exchange.com/Databases/Oracle/Q_20743275.html
0
 
LVL 7

Expert Comment

by:grim_toaster
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).
0
 
LVL 2

Expert Comment

by:vanmeerendonk
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.
0
 

Author Comment

by:preyal_infosys
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.

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 23

Expert Comment

by:seazodiac
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>
0
 

Author Comment

by:preyal_infosys
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
0
 
LVL 23

Expert Comment

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

Author Comment

by:preyal_infosys
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

0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9789373
try to set

session_cached_cursors =  60
0
 
LVL 7

Expert Comment

by:grim_toaster
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#.

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

Expert Comment

by:schwertner
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.

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now