Overview of ORA-1000 Maximum Number of Cursors Exceeded

Problem Description:
====================

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

BUGS RELATED TO ORA-01000 ERROR

Bug 4024882  ORA-1000, EXECUTING DBMS_MVIEW.REFRESH IN A PL/SQL LOOP
The complete errors look something like:
ORA-1000: maximum open cursors exceeded
ORA-6512: at "SYS.DBMS_SNAPSHOT", line
ORA-6512: at "SYS.DBMS_SNAPSHOT", line
ORA-6512: at "SYS.DBMS_SNAPSHOT", line
ORA-6512: at line

Workaround:
execute immediate 'begin dbms_mview.refresh('''||'testtab_snap'||'''); end;';
Fixed in 10.2

Several bugs with OCI and JDBC filed recently on Oracle 9i.
Bug 4518682  Connection pooling related
Bug 3065750  Cursor leak using OCIBreak



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:

[1] Oracle® Database Reference - 10g Release 2 (10.2)
   (http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams138.htm#REFRN10137)
or
[2] Oracle® Database Reference -  11g Release 1 (11.1)
   http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams138.htm#REFRN10137

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