Re: How do I close cursors?

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.jav
>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 Received on Wed Jun 30 1999 - 08:34:26 CDT