Property | Description |
---|---|
Parameter type | Integer |
Default value | 50 |
Modifiable | ALTER SYSTEM |
Range of values | 0 to 65535 |
Basic | Yes |
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 ofOPEN_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 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
java.sql.sqlException: ORA-01000: 超出打开游标的最大数
- 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();
- }
- }
- }
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,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();
- }
- }
- }
- }