oracle 游标一直涨,大量插入数据的操作老是报游标数超出最大数，怎么回事？...

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.

To solve the ORA-01000 error, set the OPEN_CURSORS to a higher number (such as 255).

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.

(在init.ora里将OPEN_CURSORS的值增大，目前生产数据库是800，此参数对性能不影响)

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.

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.

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.

(在应用层上调整RELEASE_CURSOR,HOLD_CURSOR,MAXOPENCURSORS。HOLD_CURSOR default=NO,执行语句后标记为重用,RELEASE_CURSOR default=NO,执行语句后保留,RELEASE_CURSOR,HOLD_CURSOR是相互联合影响的

----------------------------------------------------

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

(可以设置HOLD_CURSOR=NO and RELEASE_CURSOR=YES来解决错误，这时候游标cursor使用后将释放内存，所带来的就是下一次执行同样的SQL需要更长的时间，性能下降)

对于下面这个maxopencursors参数不是很理解……而且不知道这些参数在那儿设置。希望大家能够有所帮助，谢谢！

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.

[参考]

Max Open Cursor Limit

Overview of ORA-1000 Maximum Number of Cursors Exceeded