Oracle-L: RE: max open cursors exceeded

From: Ji, Richard <Richard.Ji_at_MobileSpring.com>
Date: Thu, 28 Feb 2002 06:58:24 -0800
Message-ID: <F001.0041B0AF.20020228065824@fatcity.com>

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-----
Sent: Thursday, February 28, 2002 8:34 AM To: Multiple recipients of list ORACLE-L

-----Original Message-----
Sent: Thursday, February 28, 2002 3:28 AM To: Multiple recipients of list ORACLE-L

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.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Ji, Richard
INET: Richard.Ji_at_MobileSpring.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Feb 28 2002 - 08:58:24 CST