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

前端之家收集整理的这篇文章主要介绍了oracle open_cursors的含义及ORA-01000: 超出打开游标的最大数模拟前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
Property Description
Parameter type Integer
Default value 50
Modifiable ALTER SYSTEM
Range of values 0 to 65535
Basic Yes

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

  1. import java.sql.Connection;
  2. import java.sql.DriverManager;
  3. import java.sql.ResultSet;
  4. import java.sql.sqlException;
  5. import java.sql.Statement;
  6. public class TestOpenCursor {
  7. static final String driver_class = "oracle.jdbc.driver.OracleDriver";
  8. static final String connectionURL = "jdbc:oracle:thin:@10.10.151.15:1521:orcl";
  9. static final String userID = "TEST";
  10. static final String userPassword = "TEST";
  11. public static void main(String[] args) {
  12. Connection con = null;
  13. Statement stmt = null;
  14. ResultSet rset = null;
  15. String query_string = "SELECT * FROM test where rownum=1";
  16. try {
  17. Class.forName (driver_class).newInstance();
  18. con = DriverManager.getConnection(connectionURL,userID,userPassword);
  19. for(int i=0; i<=300; i++){
  20. stmt = con.createStatement();
  21. rset = stmt.executeQuery (query_string);
  22. while (rset.next ()) {
  23. rset.getString(1);
  24. }
  25. }
  26. rset.close();
  27. stmt.close();
  28. } catch (sqlException e) {
  29. e.printStackTrace();
  30. } catch (Exception e) {
  31. e.printStackTrace();
  32. }
  33. }
  34. }
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,保证绝对的关闭

  1. import java.sql.Connection;
  2. import java.sql.DriverManager;
  3. import java.sql.ResultSet;
  4. import java.sql.sqlException;
  5. import java.sql.Statement;
  6. public class TestOpenCursor {
  7. static final String driver_class = "oracle.jdbc.driver.OracleDriver";
  8. static final String connectionURL = "jdbc:oracle:thin:@10.10.151.15:1521:orcl";
  9. static final String userID = "TEST";
  10. static final String userPassword = "TEST";
  11. public static void main(String[] args) {
  12. Connection con = null;
  13. Statement stmt = null;
  14. ResultSet rset = null;
  15. String query_string = "SELECT * FROM test where rownum=1";
  16. try {
  17. Class.forName (driver_class).newInstance();
  18. con = DriverManager.getConnection(connectionURL,userPassword);
  19. stmt = con.createStatement();
  20. for(int i=0; i<=300; i++){
  21. rset = stmt.executeQuery (query_string);
  22. while (rset.next ()) {
  23. rset.getString(1);
  24. }
  25. }
  26. rset.close();
  27. stmt.close();
  28. } catch (sqlException e) {
  29. e.printStackTrace();
  30. } catch (Exception e) {
  31. e.printStackTrace();
  32. }finally{
  33. try{
  34. if(rset != null){
  35. rset.close();
  36. }
  37. if(stmt != null){
  38. stmt.close();
  39. }
  40. }catch(Exception e){
  41. e.printStackTrace();
  42. }
  43. }
  44. }
  45. }

或者改为如下方式:

  1. package com.comtop;
  2. import java.sql.Connection;
  3. import java.sql.DriverManager;
  4. import java.sql.PreparedStatement;
  5. import java.sql.ResultSet;
  6. public class TestCursor {
  7. public static void main(String args[]) throws Exception{
  8. Connection con = null;
  9. PreparedStatement pre = null;
  10. ResultSet result = null;
  11. try{
  12. Class.forName("oracle.jdbc.driver.OracleDriver");
  13. String url = "jdbc:oracle:thin:@127.0.0.1:1521:ora11";
  14. String user = "test";
  15. String password = "test";
  16. con = DriverManager.getConnection(url,user,password);
  17. for(int i=0; i<3000; i++){
  18. String sql="select * from test where name="+i;
  19. pre = con.prepareStatement(sql);
  20. }
  21. }catch (Exception e){
  22. e.printStackTrace();
  23. }
  24. finally{
  25. try{
  26. if (result != null)
  27. result.close();
  28. if (pre != null)
  29. pre.close();
  30. if (con != null)
  31. con.close();
  32. }
  33. catch (Exception e){
  34. e.printStackTrace();
  35. }
  36. }
  37. }
  38. }

猜你在找的Oracle相关文章