Overview of ORA-1000 Maximum Number of Cursors Exceeded (文档 ID 1012266.6)

最新推荐文章于 2024-05-12 21:35:05 发布

mybluetiankong

最新推荐文章于 2024-05-12 21:35:05 发布

阅读量1.4k

点赞数

分类专栏：网络资源_ORACLE_调优

网络资源_ORACLE_调优专栏收录该内容

109 篇文章 0 订阅

订阅专栏

APPLIES TO:

Oracle Database - Enterprise Edition - Version 8.1.7.4 to 11.2.0.3 [Release 8.1.7 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 13-Jan-2012***

SYMPTOMS

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 session.
Action: Modify the program to use fewer cursors. If this error occurs often, shut down Oracle, increase the value of OPEN_CURSORS. example: alter system set open_cursors = 400 scope=both;

This error happens a lot in association with some kind of application, e,g, an 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.

CAUSE

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

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 on Oracle 9i.
Bug 4518682 Connection pooling related
Bug 3065750 Cursor leak using OCIBreak

SOLUTION

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 the Reference Guide and the Advanced Application Developer's Guide:

OracleÂ® Database Reference
10g Release 2 (10.2)
http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams138.htm#REFRN10137

11g Release 1 (11.1)
http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams153.htm#sthref464

11g Release 1 (11.2)
http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams160.htm#REFRN10137

OracleÂ® Application Developer's Reference
10g Release 2 (10.2)
http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_sqlproc.htm#sthref213

11g Release 1 (11.1)
http://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_sqlproc.htm#sthref179

11g Release 1 (11.2)
http://docs.oracle.com/cd/E11882_01/appdev.112/e25518/adfns_sqlproc.htm#sthref64

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.

REFERENCES

BUG:237091 - AFTER STATEMENT TRIGGER ON INSERT DOESN'T FIRE IF SQL STATEMENT
BUG:284745 - IN 7.1.6 STATEMENT LEVEL TRIGGERS FIRE WHEN NO ROWS ARE INSERTED
BUG:3065750 - AFTER FIXES IN BUG 2718410 & 2962298 APP WILL STILL GET ORA-1000
BUG:4024882 - ORA-1000, EXECUTING DBMS_MVIEW.REFRESH IN A PL/SQL LOOP
BUG:4518682 - TOPLINK NOT CLOSING CURSORS WHEN STATEMENT CACHING WITH EXT CONN POOL
NOTE:2055810.6 - Precompiler HOLD_CURSOR and RELEASE_CURSOR Options
NOTE:235291.1 - FAQ about OPEN and CLOSE Plsql Cursor in V$OPEN_CURSOR View

   
            相关内容 
          
              产品 
            
       Oracle Database Products > Oracle Database > Oracle Database > Oracle Database - Enterprise Edition > RDBMS
 
              关键字 
            
     10G; 
      
     11G; 
      
     AREA; 
      
     CURSOR; 
      
     HOLD_CURSOR; 
      
     INIT.ORA; 
      
     INITIALIZATION PARAMETERS; 
      
     MAXIMUM OPEN CURSORS; 
      
     MAXOPENCURSORS; 
      
     OPEN CURSORS; 
      
     RELEASE_CURSOR; 
      
     SIZING; 
      
     SQL AREA 
      
              错误 
            
     ORA-1000; 
      
     ORA-4088; 
      
     ORA-6512