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