OPEN_CURSORSspecifies 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 ofOPEN_CURSORShigh 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 byOPEN_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
CORE 11.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();
            }
    }
}

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,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();
            }
        }
    }
}