Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

ORA-01000: maximum open cursors exceeded

 
Pat Garner
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I've looked about the web for the answer to this problem but I haven't gotten a clear understanding of the problem. Neither have I discovered a practical way to fix it. I'm pasting pseudo code below, displayed in a tree-like structure so it's clear how the Connection object is being passed around. Hopefully it will be clear by looking at the pseudo code that no ResultSet nor PreparedStatement is being passed around. All ResultSets and PreparedStatements are created locally using the passed-around Connection and they are then closed locally in a finally block.
So let there be no mistake: I am closing all ResultSet and PreparedStatement objects locally. Each method that the Connection object instance gets passed into closes the ResultSet object instance and the PreparedStatement object instance within a finally block.
PopulateStudent
---------------
|
|--- Class.forName(oracleDriver);
|
|--- con = DriverManager.getConnection(oraDevURL,dbUser,dbPass);
|
|--- ps = con.prepareStatement(sb.toString());
|
|--- rs = ps.executeQuery();
|
|--- while(rs.hasNext())
| -----
| |
| |--- stu = Student.getInstance(rs.getString(),rs.getDate(), con);
| | -------
| | |
| | |---- Sequence.STUDENT.getNextId(con);
| | --------
| | |
| | |---- ps = con.prepareStatement(SELECT);
| | |
| | |---- rs = ps.executeQuery();
| | |
| | |---- if(sequence not initialized)
| | | {
| | | ps = con.prepareStatement(INIT);
| | | ps.executeUpdate();
| | | ps = con.prepareStatement(SELECT);
| | | rs = ps.executeQuery();
| | | }
| | |
| | |
| | |---- ps = con.prepareStatement(INCREMENT);
| | |
| | |---- ps.executeUpdate();
| | |
| | |---- rs.close();
| | |
| | |---- ps.close();
| |
| |--- stu.insert(con);
| -------
| |
| |---- ps = con.prepareStatement(insert.toString());
| |
| |---- for each field in the student object instance...
| | {
| | ps.setXXX(fieldIndex,fieldValue);
| | }
| |
| |---- ps.executeUpdate();
| |
| |---- ps.close();
|
|---- rs.close();
|
|---- ps.close();
|
|---- con.close();

If it's not obvious from the above diagram how the code is written and executed I'll be happy to post the code itself.
Here's the problem:
1. I get an ORA-01000: maximum open cursors exceeded after only 296 rows of student data are inserted into the student table using the Student.insert() method.
2. The problem goes away if, after every couple hundred rows have been inserted, inside the while(rs.hasNext()) I set con=null and then reinitialize con.
So it appears that the problem is the Connection object. Although rs.close() and ps.close() are properly called the Connection object does not close the cursors. This is either a big nasty bug or I'm doing something _way_ wrong. If it's a bug, this is something that is huge and should be well documented. Where's the workaround and/or bug fix? I'm using the oracle thin driver. As an aside, does anybody know for sure whether this is a driver bug or an Oracle DBMS bug?
I absolutely cannot reset the connection every couple hundred rows because I lose the ability to rollback all previously inserted rows if an exception gets thrown.
So what can I do? Any ideas would be much appreciated.
Thanks,
Pat
 
Pat Garner
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
PopulateStudent
---------------
  |
  |--- Class.forName(oracleDriver);
  |
  |--- con = DriverManager.getConnection(oraDevURL,dbUser,dbPass);
  |
  |--- ps = con.prepareStatement(sb.toString());
  |
  |--- rs = ps.executeQuery();
  |
  |--- while(rs.hasNext())
  |    -----
  |      |
  |      |--- student = Student.getInstance(rs.getString(), rs.getDate(), con);
  |      |    -------
  |      |      |
  |      |      |----  Sequence.STUDENT.getNextId(con);
  |      |          --------
  |      |            |
  |      |            |----  ps = con.prepareStatement(SELECT);
  |      |            |
  |      |            |----  rs = ps.executeQuery();
  |      |            |
  |      |            |----  if(sequence not initialized)
  |      |            |      {
  |      |            |          ps = con.prepareStatement(INIT);
  |      |            |          ps.executeUpdate();
  |      |            |          ps = con.prepareStatement(SELECT);
  |      |            |          rs = ps.executeQuery();
  |      |            |      }
  |      |            |
  |      |            |
  |      |            |----  ps = con.prepareStatement(INCREMENT);
  |      |            |
  |      |            |----  ps.executeUpdate();
  |      |            |
  |      |            |----  rs.close();
  |      |            |
  |      |            |----  ps.close();
  |      |  
  |      |--- student.insert(con);
  |           -------
  |             |
  |             |----  ps = con.prepareStatement(insert.toString());
  |             |
  |             |----  for each field in the student object instance...
  |             |      {
  |             |          ps.setXXX(fieldIndex,fieldValue);
  |             |      }
  |             |
  |             |----  ps.executeUpdate();
  |             |
  |             |----  ps.close();
  |
  |----  rs.close();
  |
  |----  ps.close();
  |
  |----  con.close();
 
Pat Garner
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Pat Garner:
I've looked about the web for the answer to this problem but I haven't gotten a clear understanding of the problem. Neither have I discovered a practical way to fix it. I'm pasting pseudo code below, displayed in a tree-like structure so it's clear how the Connection object is being passed around. Hopefully it will be clear by looking at the pseudo code that no ResultSet nor PreparedStatement is being passed around. All ResultSets and PreparedStatements are created locally using the passed-around Connection and they are then closed locally in a finally block.
So let there be no mistake: I am closing all ResultSet and PreparedStatement objects locally. Each method that the Connection object instance gets passed into closes the ResultSet object instance and the PreparedStatement object instance within a finally block.
PopulateStudent
---------------
  |
  |--- Class.forName(oracleDriver);
  |
  |--- con = DriverManager.getConnection(oraDevURL,dbUser,dbPass);
  |
  |--- ps = con.prepareStatement(sb.toString());
  |
  |--- rs = ps.executeQuery();
  |
  |--- while(rs.hasNext())
  |    -----
  |      |
  |      |--- student = Student.getInstance(rs.getString(), rs.getDate(), con);
  |      |    -------
  |      |      |
  |      |      |----  Sequence.STUDENT.getNextId(con);
  |      |          --------
  |      |            |
  |      |            |----  ps = con.prepareStatement(SELECT);
  |      |            |
  |      |            |----  rs = ps.executeQuery();
  |      |            |
  |      |            |----  if(sequence not initialized)
  |      |            |      {
  |      |            |          ps = con.prepareStatement(INIT);
  |      |            |          ps.executeUpdate();
  |      |            |          ps = con.prepareStatement(SELECT);
  |      |            |          rs = ps.executeQuery();
  |      |            |      }
  |      |            |
  |      |            |
  |      |            |----  ps = con.prepareStatement(INCREMENT);
  |      |            |
  |      |            |----  ps.executeUpdate();
  |      |            |
  |      |            |----  rs.close();
  |      |            |
  |      |            |----  ps.close();
  |      |  
  |      |--- student.insert(con);
  |           -------
  |             |
  |             |----  ps = con.prepareStatement(insert.toString());
  |             |
  |             |----  for each field in the student object instance...
  |             |      {
  |             |          ps.setXXX(fieldIndex,fieldValue);
  |             |      }
  |             |
  |             |----  ps.executeUpdate();
  |             |
  |             |----  ps.close();
  |
  |----  rs.close();
  |
  |----  ps.close();
  |
  |----  con.close();

If it's not obvious from the above diagram how the code is written and executed I'll be happy to post the code itself.
Here's the problem:
1. I get an ORA-01000: maximum open cursors exceeded after only 296 rows of student data are inserted into the student table using the Student.insert() method.
2. The problem goes away if, after every couple hundred rows have been inserted, inside the while(rs.hasNext()) I set con=null and then reinitialize con.
So it appears that the problem is the Connection object. Although rs.close() and ps.close() are properly called the Connection object does not close the cursors. This is either a big nasty bug or I'm doing something _way_ wrong. If it's a bug, this is something that is huge and should be well documented. Where's the workaround and/or bug fix? I'm using the oracle thin driver. As an aside, does anybody know for sure whether this is a driver bug or an Oracle DBMS bug?
I absolutely cannot reset the connection every couple hundred rows because I lose the ability to rollback all previously inserted rows if an exception gets thrown.
So what can I do? Any ideas would be much appreciated.
Thanks,
Pat
 
Wayne L Johnson
Ranch Hand
Posts: 399
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's a little hard to tell what's going on w/out seeing the actual code. But you might want to double check to see if there is any method that is using the same "rs" twice but only closing it once. For example, looking at your pseudo-code for the "Student.getInstance()" method (?) I see the following:

As I said, it's hard to see what's REALLY going on w/out the actual code, but my guess is that you're using the "rs" variable to hold on to different ResultSet instances w/out closing previous ones.
If this is not it, please supply the source code.
 
Pat Garner
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yessirree Wayne you surely were right! I was reusing the ps (PreparedStatement) and rs (ResultSet) variables multiple times without calling close() in between, and this is what caused the database cursor leak. Thanks for your help!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic