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

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 30 Jun 1999 13:06:30 GMT
Message-ID: <377a1642.635473@newshost.us.oracle.com>

A copy of this was sent to ckuczbor_at_my-deja.com (if that email address didn't require changing) On Tue, 29 Jun 1999 20:42:46 GMT, you 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.

Is it possible that you are calling lots of routines that each declare their own "Statement" variables and let these go out of scope without calling .close()?

For example, i think I am able to replicate your issue with the following code.

I have a table T ( x number );
my open_cursors is set to 1,000

the following code works:

import java.sql.*;
import oracle.jdbc.driver.*;

class test {
  public static void main (String args []) throws SQLException {

    // Load Oracle driver
    DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());

    // Connect to the local database
    Connection conn =

      //DriverManager.getConnection ("jdbc:oracle:thin:@slackdog:1521:oracle8",
      DriverManager.getConnection ("jdbc:oracle:oci8:@slackdog.world",
                                   "scott", "tiger");

    // Query the employee names
    Statement stmt = conn.createStatement ();

    stmt.execute( "delete from t" );

    for( int i = 0; i <= 2000; i++)
    {
    Statement NewStmt = conn.createStatement ();

        NewStmt.execute( "INSERT INTO T VALUES (" + i + ")" );
        NewStmt.close();

    }
}
}

**but** if you comment out the NewStmt.close(); inside the loop, it fails with max open cursors after a while. That is because the statement is just going out of scope inside the loop for each iteration -- and we have not closed the cursor. As there are no destructors in java, we can't clean up cursors automagically -- java will clean up unused memory but it won't clean up 'everything' possible.

Are you ever closing statements?

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Jun 30 1999 - 08:06:30 CDT