Re: Cursors consumed to fast :-(

Martin Schroeder wrote:
>
> 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...

  Try to set CLOSE_CACHED_OPEN_CURSORS to TRUE in the initialization parameter file.

---
Name   : Lun Wing San (Certified Oracle Database Administrator)

Title  : Oracle Database Administrator and System Administrator of QRC
Phone  : (852)27885841

This posting represents the personal opinions of the author. It is not the
official opinion or policy of the author's employer. Warranty expired when you
opened this article and I will not be responsible for its contents or use.