Oracle-L: RE: open cursors problem

From: Rachel Carmichael <wisernet100_at_yahoo.com>
Date: Mon, 29 Jul 2002 05:13:22 -0800
Message-ID: <F001.004A4732.20020729051322@fatcity.com>

I think the problem is that that's the value in the init.ora file for his database, NOT the limit of Oracle itself.

According to the docs, the maximum number of open cursors you can allow in 8.1.7 AND 9.2 is 1 to 4294967295 (4 GB -1). And that limit is for each individual user, not cumulative total for the database.

so the "crash" may be that the parameter is set to 750 in the initialization file for that database, in which case, if you hit it then you need to raise that number in the init.ora and bounce the database.

If that's not the problem, then I would seriously review my code. Why ANY individual user would have that many open cursors in a single session is something to check. Usually means the app is written not to clean up after itself. There IS a limit to how much memory you can use before the machine hangs itself with constant swapping.

I had one like that in 7.3.4, the programmers never bothered to explicitly close the cursors when done, they let it default to when the session was ended, the end users would click open new sessions without closing down old ones. We finally set open_cursors to 1000 and let it fail, to teach the end-users to close windows. Why didn't the programmers rewrite the code? Damagement there decided that the programmers knew better than the DBA about how things should work in conjunction with the database. And, of course, fixing bad code doesn't show as "progress" on status reports.

"Toepke, Kevin M" <ktoepke_at_trilegiant.com> wrote:
> WHAT! A cap of 750 open cursors in the database? Where did you hear
> that?
>
> -----Original Message-----
> Sent: Monday, July 29, 2002 3:38 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi everyone,
> I am running EJB under weblogic 5.1 jdbc 2.0,
> and
> when I last checked the database the number of open cursors was 564.
> I know
> 8.1.7 has a cap of 750 open cursors after which the db will crash.
> Since
> this is the first time I have faced this problem I would be grateful
> if
> anyone could point out what could be the possible areas that could
> cause
> this problem ( other than the logical one of not closing cursors once
> they
> have been opened and used !).
>
> any help here would be appreciated.
>
> thank you,
>
> Gavin
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Gavin D'Mello
> INET: gavin_d_at_tis.co.in
>
> 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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Toepke, Kevin M
> INET: ktoepke_at_trilegiant.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).

Do You Yahoo!?
Yahoo! Health - Feel better, live better http://health.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachel Carmichael
  INET: wisernet100_at_yahoo.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 Mon Jul 29 2002 - 08:13:22 CDT