Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: open cursors problem

RE: open cursors problem

From: Rachel Carmichael <>
Date: Mon, 29 Jul 2002 05:13:22 -0800
Message-ID: <>

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.

Do You Yahoo!?
Yahoo! Health - Feel better, live better
Please see the official ORACLE-L FAQ:
Author: Rachel Carmichael

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: (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

Original text of this message