RE: SQLException: java.sql.SQLException: ORA-01000: maximum open

Glenn :
  I am having this same problem today. on an 8.1.5 database. So I have been checking into this. From the past posts to this list, it appears that the open cursors in v$OPEN_CURSOR do not necessarily have a relationship to the number of cursors that are actually open. I am going to try setting _close_cached_open_cursors = true in the init.ora file of my development db. hth, Mary Ruiz

Here are 3 notes from Metalink:
From: Oracle, krishna kumar sivasubramanian 03-Jul-00 10:28 Subject: Re : Oracle 8.1.6 - CLOSE_CACHED_OPEN_CURSORS Hello

In 8.1.6 the parameter CLOSE_CACHED_OPEN_CURSORS is hidden with an
_underscore.You can set the above paramter to true as shown below

_CLOSE_CACHED_OPEN_CURSORS
From: Oracle, Ashok CG 18-Oct-00 11:58
Subject: Re : Closing cursors

Hi,

It is always good to close explicit cursor. Even though the cursor is closed it still remains in the cache based on the parameter value CLOSE_CACHED_OPEN_CURSORS. This is a performance optimization method used by the server. Even though you have CLOSED the cursors, they are still cached in Server memory. The CLOSE_CACHED_OPEN_CURSORS parameter in init.ora forces all of these cached cursors to be closed at the end of each transaction, if that is desired.

Hence, instead of server deciding to close the cursor, it would be better to close it explicitly.

Regards,
Ashok CG

From: Oracle, krishna kumar sivasubramanian 07-Sep-00 07:19 Subject: Re : Re : How does a cursor get closed?

Hello

The server caches cursors opened by the PL/SQL engine. Each time a cursor is closed explicitly in the pl/sql programme, it is really moved to an Least Recently Used (LRU) list of open cursors and left open.

Note that no optimization occurs. In other words, the cursor caching code does not check to see if a particular query already has a cursor open; it simply creates another one. This is done until OPEN_CURSORS is reached. If you have OPEN_CURSORS cached as open, however, then if an identical query occurs, the server reuses the cached cursor.

Setting the CLOSE_CACHED_OPEN_CURSORS parameter to true forces all of these cached cursors to be closed at the end of each transaction.

Regards
S.Krishna Kumar

-----Original Message-----
From: Glenn Travis
Sent: Monday, November 06, 2000 11:06 AM To: Multiple recipients of list ORACLE-L Subject: SQLException: java.sql.SQLException: ORA-01000: maximum open cursors

We are receiving this error from our app (which is using JSP);  SQLException: java.sql.SQLException: ORA-01000: maximum open cursors exceeded

Database is 8.1.6.

open_cursors is set to 500 in the init.ora param file (we upped this from 100 last week).

When I check the open_cursors from the v$open_cursor view, I never see the values anywhere near 500!

Output of query;
 select saddr,sid,user_name,count(*) countcur from v$open_cursor group by saddr,sid,user_name
  order by 4,3

SADDR           SID USER_NAME                        COUNTCUR
-------- ---------- ------------------------------ ----------
8C0EF908         69 SYS                                     1
8C0E1A48         42 ESPADMIN                                3
8C0D4C08         17 CND                                     4
8C0D9E88         27 CNDI                                    4
8C0F2248         74 ESPADMIN                                4
8C0E6488         51 ESPADMIN                                5
8C0E2AC8         44 CND                                     6
                                                   ----------
sum                                                        27

Isn't the open_cursor param setting the open cursors per SESSION? If my value is 500, why do I keep getting the ORA-01000 error??? What is going on here? Am I seeing another param limit being reached and the ORA-01000 error getting thrown? Any help per advice is greatly appreciated.

PS. I am aware (from previous posts) that the v$open_cursor table actually shows ALL cursors (open,closed,cached), not just open cursors. I issue 'alter system flush shared_pool' and the output from this table goes down to 1 or 2. Then it starts to climb back up again. BUT it never reaches 500!

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Glenn Travis
  INET: Glenn.Travis_at_wcom.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