| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: How do I close cursors?
A copy of this was sent to Carol Kuczborski <carol.kuczborski_at_eds.com>
(if that email address didn't require changing)
On Wed, 30 Jun 1999 19:18:46 GMT, you wrote:
>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.
>
Can you post me an example that fails -- one run against scott/tiger?
I read that bug as well -- the test case supplied calls PLSQL and as stated below:
>> 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).
>>
...
>> 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
the cursors there were noted as being opened where those executed by PLSQL. This is normal, expected, and why the bug was closed.
to see this behavior try this:
SQL> l
1 create or replace procedure foo
2 as
3 begin
4 for x in ( select * from emp ) loop 5 dbms_output.put_line( x.ename ); 6 end loop;
SQL> exec foo
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
PL/SQL procedure successfully completed.
SQL> select * from v$open_cursor where user_name = 'TKYTE';
SADDR SID USER_NAME ADDRESS HASH_VALUE -------- ---------- ------------------------------ -------- ----------SQL_TEXT
8006E340 58 TKYTE 80B4FE6C 1299113472SELECT * FROM EMP
8006E340 58 TKYTE 80C25B94 806441549select * from v$open_cursor where user_name = 'TKYTE'
See how the SELECT * FROM EMP is there -- plsql has cached it.
Now, if i do somethhing like:
SQL> alter session set close_cached_open_cursors = TRUE;
Session altered.
SQL> select * from v$open_cursor where user_name = 'TKYTE';
SADDR SID USER_NAME ADDRESS HASH_VALUE -------- ---------- ------------------------------ -------- ----------SQL_TEXT
8006E340 58 TKYTE 80B4FE6C 1299113472SELECT * FROM EMP
8006E340 58 TKYTE 80B2C84C 806441549select * from v$open_cursor where user_name = 'TKYTE'
SQL> commit;
Commit complete.
SQL> select * from v$open_cursor where user_name = 'TKYTE';
SADDR SID USER_NAME ADDRESS HASH_VALUE -------- ---------- ------------------------------ -------- ----------SQL_TEXT
8006E340 58 TKYTE 8078D6F4 806441549select * from v$open_cursor where user_name = 'TKYTE'
The close_cached_opened_cursors will get rid of the cached cursors. this method works upto 8.0 of Oracle. They have changed the algorithms and removed this parameter.
Its not jdbc -- its plsql. its the way its supposed to work. you can control with it session_cached_cursors as well...
>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
>>
--
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 - 15:53:11 CDT
 |
 |