Re: ORA-01000 max cursors error received using Oracle and JDBC

I am reporting on the outcome of our problem. We were closing statements and result sets, but still received the ORA-01000 too many open cursors error. We discovered that in several cases, we were not closing our statements and result sets "within scope". We opened the cursor in one function, called another function, then closed the statement and result set. We cleaned up our application code to close all statements and result sets in the same function that opened them.

We also discovered, that the v$open_cursor view only reports VALID, INSTANTIATED open cursors. In our case, the cursors were closed "out of scope". They did not appear in v$open_cursor, but were being reported in the v$sesstat and v$sysstat views for statistic #3 (current opened cursors) which reports all open cursors, regardless of their status. Therefore, we were continuing to receive the ORA-01000 error even though v$open_cursor did not report any open cursors. We could not see the open cursors, because we had been instructed to look at the v$open_cursor view, and not the v$sesstat or v$sysstat views.

Lessons learned:

1. Close all statements and result sets "in scope".
2. Use v$sesstat and v$sysstat, in addition to v$open_cursor to monitor ALL open cursors (valid and invalid)

---
Carol Kuczborski
EDS/DEIS In article <7malrt$26d$1_at_nnrp1.deja.com>,   ptelang_at_my-deja.com wrote:
> You need to close any statements you opened.
> For eg:
> Connection conn = DriverManager.getConnection(...);
> String sql = "SELECT name FROM emp";
> Statement stmt = conn.createStatement();
> ResultSet rs = stmt.executeQuery(sql);
> while(rs.next()){
> System.out.println(rs.getString(1));
> }
> stmt.close(); // This method will close the cursor
> Make sure you are doing appropriate exception handling
> and closing the statement even if there is an exception.
> This should solve your problem..
> -->
> Pankaj
>
> In article <7lbb44$qvb$1_at_nnrp1.deja.com>,
> ckuczbor_at_my-deja.com wrote:
> > We are receiving the ORA-01000 (maximum open
> > cursors exceeded) error when running a load test
> > on our web application. We are using JAVA
> > servlets, Oracle 7.3.4.2, JDBC thin driver, IBM
> > WebSphere Application Server (using their DB
> > connection pool manager), and Netscpape
> > Enterprise Web Server.
> >
> > It appears that the cursors are not being closed
> > or released when the servlet is finished getting
> > the data. We have the Oracle max cursor
> > configuration parameter set to 400. However, we
> > do not want to increase this any higher. Our
> > problem is that the cursors are not being
> > released when the SQL call is complete, and
> > therefore the number of open cursors keeps
> > growing. We want the cursors to be closed or
> > released when the servlet has completed all
> > processing.
> >
> > Has anyone else had this issue and what was the
> > final solution?
> >
> > Sent via Deja.com http://www.deja.com/
> > Share what you know. Learn what you don't.
> >
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Jul 19 1999 - 15:54:11 CDT