Re: ORA-1000 and pl/sql cursor cache

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 <DLord_at_ironmountain.co.uk>
> Date: 2003/11/26 Wed AM 10:34:34 EST
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> 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: DLord_at_ironmountain.co.uk
>
> 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: 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.net
-- 
Author: <ryan_oracle_at_cox.net
  INET: ryan_oracle_at_cox.net

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