| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Cursors consumed to fast :-(
Martin,
PL/SQL keeps cursors in memory even when you CLOSE them. This is to avoid re-parsing when the cursor is re-opened. You can force PL/SQL to close these cursors on COMMIT or ROLLBACK by setting CLOSE_CACHED_OPEN_CURSORS=TRUE in the init.ora or in an ALTER SESSION statement.
Setting this to TRUE will reduce the number of open cursors, but may increase your parse overhead when you re-open cursors after the commit. Increasing OPEN_CURSORS may be a better solution.
Guy
Martin Schroeder <ms_at_dream.hb.north.de> wrote in article
<1uuJzA9FBh108h_at_dream.hb.north.de>...
> Hi,
> I have constructed a PL/SQL program consisting of some PL/SQL packages
that
> uses implicit cursors and explicit cursors from DBMS_SQL. Mostly the
cursors
> are used implicitely via Loops; at the places where the cursors are used
> explicitely (via DBMS_SQL) they are closed after use.
> The program currently does no COMMIT; it ROLLBACKs in case of errors.
>
> I call the program/routines via execute statements from SQL*Plus.
>
> But I frequently get the ORA-01000 error -- maximum number of open
cursors
> exceeded. OPEN_CURSOR is set to 50.
>
> When I examine the cursors via v$open_cursor I find indeed many cursors
(e.g.
> from triggers); but these remain even after an explicit COMMIT or
ROLLBACK. :-((
>
> I am quite sure that I close all cursors the program knows of...
>
> Anybody got a clue for me?
>
> Thanks in advance
> Martin
>
> --
> Martin Schr"oder, MS_at_Dream.HB.North.DE
> There are two ways of constructing a software design: One way is to make
> it so simple that there are obviously no deficiencies, and the other way
> is to make it so complicated that there are no obvious deficiencies. The
> first method is far more difficult. (C. A. R. Hoare)
>
Received on Fri Mar 14 1997 - 00:00:00 CST
 |
 |