are you looking for the init.ora max_open_cursors(dont think i typed it exactly right).
even if the cursors are cached, they should not be counted as open. they doesnt make sense from an oracle design standpoint. > > From: Lord David <[EMAIL PROTECTED]> > Date: 2003/11/26 Wed AM 10:34:34 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: ORA-1000 and pl/sql cursor cache > > Hi > > Does anyone know whether its possible to control the size of the pl/sql > static cursor cache. > > I'm running into ORA-01000: maximum number of open cursors exceeded errors > and part of the problem (apart from the usual developers not closing > explicit cursors) is that _all_ static sql statements in compiled pl/sql > units seem to be getting cached. I can't find any documentation of this > feature apart from a few hints in the pl/sql and application development > docs. Here's an example from an 8.1.7 database: - > > SQL>create or replace procedure foobar is > 2 v_result varchar2(30); > 3 begin > 4 select user into v_result from dual; > 5 end; > 6 / > > Procedure created. > > SQL> > SQL>select b.sql_text > 2 from v$session a, v$open_cursor b > 3 where a.sid = b.sid > 4 and a.audsid = userenv('SESSIONID') > 5 / > > SQL_TEXT > ------------------------------------------------------------ > SELECT SYS_CONTEXT(:b1,:b2) FROM SYS.DUAL > select b.sql_text from v$session a, v$open_cursor b where a. > > SQL> > SQL>exec foobar > > PL/SQL procedure successfully completed. > > SQL> > SQL>select b.sql_text > 2 from v$session a, v$open_cursor b > 3 where a.sid = b.sid > 4 and a.audsid = userenv('SESSIONID') > 5 / > > SQL_TEXT > ------------------------------------------------------------ > SELECT SYS_CONTEXT(:b1,:b2) FROM SYS.DUAL > select b.sql_text from v$session a, v$open_cursor b where a. > SELECT USER FROM DUAL > > TIA > -- > David Lord > Senior DBA > Iron Mountain Europe > > > *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** > This e-mail and its attachments are intended for the > author's addressee only and may be confidential. > > If they have come to you in error you must take no > action based on them, nor must you copy or show > them to anyone; please reply to this e-mail and > highlight the error. > > Please note that this e-mail has been created in the > knowledge that Internet e-mail is not a 100% secure > communications medium. We advise that you > understand and observe this lack of security when > e-mailing us. Steps have been taken to ensure this > e-mail and attachments are free from any virus, but > advise the recipient to ensure they are actually virus > free. > > The views, opinions and judgments expressed in this > message are solely those of the author. The message > contents have not been reviewed or approved by Iron > Mountain. > > *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Lord David > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (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.net -- Author: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).