Re: open cursors problem

Dennis William sent me a mail containing the discussions on this same topic a couple of weeks back. That discussion layed the blame a bit on the database config parameters in the weblogic properties file like  initialCapacity, maxCapacity, capacityIncrement

Is this really the case ? or just developer error of not closing the prepared statements ?

Gavin

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Monday, July 29, 2002 6:43 PM

> 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).

-- 
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).