RE: max open cursors exceeded

[Ji, Richard]

Title: max open cursors exceeded

There is a bug(well, it's a feature actually) that if you uses JDBC even if you close all of your

resultsets and statements, the cursors remain open by Oracle unless you close the Connection.

-----Original Message-----
From: Gogala, Mladen [mailto:[EMAIL PROTECTED]]
Sent: Thursday, February 28, 2002 8:34 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: max open cursors exceeded

 

-----Original Message-----
From: Daiminger, Helmut [mailto:[EMAIL PROTECTED]]
Sent: Thursday, February 28, 2002 3:28 AM
To: Multiple recipients of list ORACLE-L
Subject: max open cursors exceeded

a) MAX_OPEN_CURSORS is set to 1000 - when exceeding that threshold, we're experiencing problems; how can we monitor which users uses up all the cursors (and how many). 

By monitoring v$open_cursor 

b) What exactly is displayed when querying v$open_cursor? - I know htat there are parsed statements of the user/session - but this should be more than just a PL/SQL "declare cursor... open...fetch...close cursor", right? What about statements issued in SQL*Plus or through JDBC etc.? 

v$open_cursor displays ALL the cursors for a given session, not just the explicite PL/SQL or PRO*C stuff.A cursor is needed for EVERY SQL STATEMENT. The only cursor not displayed by v$open_cursor is the DBA himself/herself, especially if he has to support tools that do their own multiplexing, like Forte.

 

c) The results from v$open_cursor is equivalent to the currently running transaction, right? So when are those entries removed again? When the transaction commits or when the sessions ends? Or when issuing a PL/SQL "close cursor". Or are those entries overwritten? by whom? when? 

An implicit cursor is closed when another statement is opened and re-parsed. If you take a look at v$session, you'll find that is contains the "current SQL" address. That is, basically, the open cursor that the session is currently executing. When the next SQL comes in, this cursor will be closed, SQL put in the library cache and a new cursor will  be allocated.

d) what does querying "select * from v$sql_cursor" return?  

That query will usually return enough information for your screen to join it's ancestors and will leave you wandering why is that SQL*Plus does not have "clear" or "cls" command.

Any ideas?  

Yes. I want to win a lottery jackpot and start my own business in close proximity to the Waikiki beach.