oracle open_cursors的含义及ORA-01000: 超出打开游标的最大数模拟

秒速五厘米 2022-09-21 13:28 12阅读 0赞

<table title="OPEN_CURSORS initialization parameter" style="background-color:white; margin-bottom:1.25rem; border:1px; border-collapse:collapse; table-layout:fixed; width:951px; border-spacing:0px; padding:3px; max-width:90em"> 
 <thead style="background-color:rgb(245,245,245)"> 
 <tr style="background-color:rgb(63,63,63)!important; vertical-align:top"> 
 <th style="margin:0px; padding:0.5rem 0.625rem 0.625rem; word-wrap:break-word; color:rgb(34,34,34); font-size:0.875rem; line-height:1.125rem; vertical-align:bottom"> Property</th> 
 <th style="margin:0px; padding:0.5rem 0.625rem 0.625rem; word-wrap:break-word; color:rgb(34,34,34); font-size:0.875rem; line-height:1.125rem; vertical-align:bottom"> Description</th> 
 </tr> 
 </thead> 
 <tbody style=""> 
 <tr style="vertical-align:top; padding:8px 6px; border-bottom-width:1px; border-bottom-style:solid; border-bottom-color:rgb(63,63,63); background-color:white!important"> 
 <td style="margin:0px; padding:0.5625rem 0.625rem; word-wrap:break-word; font-size:0.875rem; color:rgb(34,34,34); line-height:1.125rem"> Parameter type</td> 
 <td style="margin:0px; padding:0.5625rem 0.625rem; word-wrap:break-word; font-size:0.875rem; color:rgb(34,34,34); line-height:1.125rem"> Integer</td> 
 </tr> 
 <tr style="vertical-align:top; padding:8px 6px; border-bottom-width:1px; border-bottom-style:solid; border-bottom-color:rgb(63,63,63); background-color:rgb(249,249,249)"> 
 <td style="margin:0px; padding:0.5625rem 0.625rem; word-wrap:break-word; font-size:0.875rem; color:rgb(34,34,34); line-height:1.125rem"> Default value</td> 
 <td style="margin:0px; padding:0.5625rem 0.625rem; word-wrap:break-word; font-size:0.875rem; color:rgb(34,34,34); line-height:1.125rem"> <code style="font-size:13px; color:rgb(0,0,0); line-height:1.615; padding:3px 0px">50</code></td> 
 </tr> 
 <tr style="vertical-align:top; padding:8px 6px; border-bottom-width:1px; border-bottom-style:solid; border-bottom-color:rgb(63,63,63); background-color:white!important"> 
 <td style="margin:0px; padding:0.5625rem 0.625rem; word-wrap:break-word; font-size:0.875rem; color:rgb(34,34,34); line-height:1.125rem"> Modifiable</td> 
 <td style="margin:0px; padding:0.5625rem 0.625rem; word-wrap:break-word; font-size:0.875rem; color:rgb(34,34,34); line-height:1.125rem"> <code style="font-size:13px; color:rgb(0,0,0); line-height:1.615; padding:3px 0px">ALTER SYSTEM</code></td> 
 </tr> 
 <tr style="vertical-align:top; padding:8px 6px; border-bottom-width:1px; border-bottom-style:solid; border-bottom-color:rgb(63,63,63); background-color:rgb(249,249,249)"> 
 <td style="margin:0px; padding:0.5625rem 0.625rem; word-wrap:break-word; font-size:0.875rem; color:rgb(34,34,34); line-height:1.125rem"> Range of values</td> 
 <td style="margin:0px; padding:0.5625rem 0.625rem; word-wrap:break-word; font-size:0.875rem; color:rgb(34,34,34); line-height:1.125rem"> 0 to 65535</td> 
 </tr> 
 <tr style="vertical-align:top; padding:8px 6px; border-bottom-width:2px!important; border-bottom-style:solid!important; border-bottom-color:rgb(63,63,63)!important; background-color:white!important"> 
 <td style="margin:0px; padding:0.5625rem 0.625rem; word-wrap:break-word; font-size:0.875rem; color:rgb(34,34,34); line-height:1.125rem"> Basic</td> 
 <td style="margin:0px; padding:0.5625rem 0.625rem; word-wrap:break-word; font-size:0.875rem; color:rgb(34,34,34); line-height:1.125rem"> Yes</td> 
 </tr> 
 </tbody> 
</table>

`OPEN_CURSORS` specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once. You can use this parameter to prevent a session from opening an excessive number of cursors.

It is important to set the value of `OPEN_CURSORS` high enough to prevent your application from running out of open cursors. The number will vary from one application to another. Assuming that a session does not open the number of cursors specified by `OPEN_CURSORS`, there is no added overhead to setting this value higher than actually needed.

OPEN\_CURSORS是一个session一次最多打开的游标数量，就是执行SQL的数量，一般有问题是程序写的有问题。

SQL> select \* from v$version;  
BANNER  
\---------------------------------------------------------------------------  
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production  
PL/SQL Release 11.2.0.3.0 - Production  
CORE11.2.0.3.0Production  
TNS for Linux: Version 11.2.0.3.0 - Production  
NLSRTL Version 11.2.0.3.0 - Production  
  
SQL> show parameter open\_cursor  
NAME                                 TYPE        VALUE  
\------------------------------------ ----------- -------------  
open\_cursors                         integer     300

import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
 public class TestOpenCursor {
 static final String driver_class = "oracle.jdbc.driver.OracleDriver";
 static final String connectionURL = "jdbc:oracle:thin:@10.10.151.15:1521:orcl";
 static final String userID = "TEST";
 static final String userPassword = "TEST";
 public static void main(String[] args) {
 Connection con = null;
 Statement stmt = null;
 ResultSet rset = null;
 String query_string = "SELECT * FROM test where rownum=1";
 try {
 Class.forName (driver_class).newInstance();
 con = DriverManager.getConnection(connectionURL, userID, userPassword);
 for(int i=0; i<=300; i++){
 stmt = con.createStatement();
 rset = stmt.executeQuery (query_string);
 while (rset.next ()) {
 rset.getString(1);
 }
 }
 rset.close();
 stmt.close();
 } catch (SQLException e) {
 e.printStackTrace();
 } catch (Exception e) {
 e.printStackTrace();
 }
 }
 }

**java.sql.SQLException: ORA-01000: 超出打开游标的最大数**  
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)  
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)  
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)  
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)  
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)  
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)  
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)  
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:873)  
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167)  
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289)  
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1491)  
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:406)  
at TestOpenCursor.main(TestOpenCursor.java:22)  
**上面的代码有两个问题：1.循环创建了Statement，而关闭在循环外。如果关闭也放在循环内，可以避免ORA-01000的错误，但效率不高，每次打开与关闭消耗太多的时间。 2.最后要写finally,保证绝对的关闭。**

import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
 public class TestOpenCursor {
 static final String driver_class = "oracle.jdbc.driver.OracleDriver";
 static final String connectionURL = "jdbc:oracle:thin:@10.10.151.15:1521:orcl";
 static final String userID = "TEST";
 static final String userPassword = "TEST";
 public static void main(String[] args) {
 Connection con = null;
 Statement stmt = null;
 ResultSet rset = null;
 String query_string = "SELECT * FROM test where rownum=1";
 try {
 Class.forName (driver_class).newInstance();
 con = DriverManager.getConnection(connectionURL, userID, userPassword);
 stmt = con.createStatement();
 for(int i=0; i<=300; i++){
 rset = stmt.executeQuery (query_string);
 while (rset.next ()) {
 rset.getString(1);
 }
 }
 rset.close();
 stmt.close();
 } catch (SQLException e) {
 e.printStackTrace();
 } catch (Exception e) {
 e.printStackTrace();
 }finally{
 try{
 if(rset != null){
 rset.close();
 }
 if(stmt != null){
 stmt.close();
 }
 }catch(Exception e){
 e.printStackTrace();
 }
 }
 }
 }

或者改为如下方式：

package com.comtop;
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 public class TestCursor {
 public static void main(String args[]) throws Exception{
 Connection con = null;
 PreparedStatement pre = null;
 ResultSet result = null;
 try{
 Class.forName("oracle.jdbc.driver.OracleDriver");
 String url = "jdbc:oracle:thin:@127.0.0.1:1521:ora11";
 String user = "test";
 String password = "test";
 con = DriverManager.getConnection(url, user, password);
 for(int i=0; i<3000; i++){
 String sql="select * from test where name="+i;
 pre = con.prepareStatement(sql);
 }
 }catch (Exception e){
 e.printStackTrace();
 }
 finally{
 try{
 if (result != null)
 result.close();
 if (pre != null)
 pre.close();
 if (con != null)
 con.close();
 }
 catch (Exception e){
 e.printStackTrace();
 }
 }
 }
 }