Re: How do I close cursors?

Thomas,

I did find something in the ORACLE TAR/BUG database at Metalink specifically addressing my same issue.

It is Bug no. 864760
Filed 04-Apr-1999
Statement: SSP4: Open cursors are not released on DB after JDBC statements are closed

Basically, the response was that the tech person did not feel it was a bug. "If you are getting an ORA-01000, then it is not because of cursors left open by PL/SQL or JDBC. While they do stay for the life of the session, they are re-used as needed."

However, we do not see the cursors being re-used, only more created, until we reach the max.

How do you check to see if cursors are being re-used?

In article <377b1be8.2081523_at_newshost.us.oracle.com>,   tkyte_at_us.oracle.com wrote:
> 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 22:32:16 GMT, you wrote:
>
> >Thomas,
> >
> >I am having the same problem with open cursors not being released.
We
> >have a system that was suppose to go into production this week for
the
> >Department of Defense, but has been postponed due this problem. In
our
> >application code, we are issuing a Statement.close call for each SQL
> >statement executed, even if it gets an exception. Some of the
> >discussion threads at Deja.com have mentioned also closing the Result
> >Set, however we do not do this. do the result sets also need to be
> >closed? Is there a bug with the Oracle JDBC driver?
> >
>
> the result sets don't *need* to be closed to free the cursor (but as
good
> programmers we would close them to make sure resources were freed).
>
> the statements associated with the results DO need to be closed to
free the
> cursor.
>
> did you know that plsql caches cursors? What is your open_cursors set
to (it
> should be way above the default -- i typically run with 500 or more).
>
> did you know you can log into another session and query v$open_cursor
where
> username = 'SOMETHING' to see the open cursors a user has? that'll
help you
> track down where the cursors are coming from (if you see a bunch of
cursors you
> execute in stored procedures there -- then it is plsql caching them
and you can
> use close_cached_open_cursors to control that behaviour although
upping
> open_cursors is better). If you see them coming from your application
(you'll
> see the text of the query), then you can look at the code that
executes that
> query and verify you are closing that statement.
>
> AFAIK, there are no bugs with respect to cursor leaking with the jdbc
drivers.
> i have not be able to produce one in testing, nor can I find any
references to
> it in the problem database.
>
> >In article <3772c4ff.174361168_at_newshost.us.oracle.com>,
> > tkyte_at_us.oracle.com wrote:
> >> A copy of this was sent to tasos_at_csa.bu.edu (Anastasios Kotsikonas)
> >> (if that email address didn't require changing)
> >> On 23 Jun 1999 00:45:29 GMT, you wrote:
> >>
> >> >
> >> >Hi all,
> >> >
> >> >another newbie question...
> >> >
> >> >I get this exception from my Java app connecting to 8i via JDBC:
> >> >
> >> >Internal Error:
> >> >java.sql.SQLException: ORA-01000: maximum open cursors exceeded
> >> >
> >> > at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:181)
> >> > at oracle.jdbc.ttc7.Oopen.receive(Oopen.java:127)
> >> > at

oracle.jdbc.ttc7.TTC7Protocol.open(TTC7Protocol.java:350)
> >> > at
> >oracle.jdbc.driver.OracleStatement.<init>(OracleStatement.java:129)
> >> > at
>
>oracle.jdbc.driver.OracleConnection.createStatement(OracleConnection.ja
v
> >a:128)
> >> > at lwdb.LWList.GetByName(LWList.java:1944)
> >> > at lwd.List.getList(List.java:436)
> >> > at lwd.action.addUser.Begin(addUser.java:154)
> >> > at lwd.dispatch.WorkerThread.run(WorkerThread.java:246)
> >> >java.lang.Exception: Caught in:
> >> > at

lwd.cos.email.error.RuntimeError.run(RuntimeError.java:86)
> >> > at
> >lwd.cos.email.error.RuntimeError.<init>(RuntimeError.java:62)
> >> > at lwd.action.addUser.Begin(addUser.java:223)
> >> > at lwd.dispatch.WorkerThread.run(WorkerThread.java:246)
> >> >
> >> >what's causing this?
> >> >
> >> >thanks
> >> >
> >> >tasos
> >>
> >> 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
> >>
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Share what you know. Learn what you don't.
>
> --
> 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
>

--
Carol Kuczborski
EDS/DEIS II Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Jun 30 1999 - 14:18:46 CDT