Re: ORA-01000-Maximum open cursors exceeded

Preetha Suri <Preetha.Suri_at_sisl.co.in> wrote in message news:9abjgm$sn$1_at_news.mch.sbs.de...
>
> ORA-01000:maximum open cursors exceeded.
>
> I would like to solve this problem without resetting the number of
> OPEN_CURSORS in ORACLE.

If you go to www.deja.com (or
http://groups.google.com/googlegroups/deja_announcement.html) you can search through newsgroups and will find the answers to your questions, e.g.:

http://groups.google.com/groups?hl=en&lr=&safe=off&ic=1&th=58941809263d1a79& seekd=948570159#948570159

And here's the best bit:

> Newsgroups: comp.databases.oracle.server
> Date: 2000-12-22 12:20:10 PST
> In article <92094h$ml6$1_at_nnrp1.deja.com>,
> David Fitzjarrell <oratune_at_aol.com> wrote:
 

> PL/SQL implicitly declares a cursor for all SQL data manipulation
> statements, including queries that return only one row. For queries
> that return more than one row, you can explicitly declare a cursor to
> process the rows individually.
>
> A cursor is a handle to a specific private SQL area. Although most
> Oracle users rely on the automatic cursor handling of the Oracle
> utilities, the programmatic interfaces offer application designers more
> control over cursors. In application development, a cursor is a named
> resource available to a program, which can be specifically used for
> parsing SQL statements embedded within the application.
>
> There is no absolute limit to the total number of cursors one session
> can have open at one time, subject to two constraints:
>
> Each cursor requires virtual memory, so a session's total number of
> cursors is limited by the memory available to that process.
>
> A system-wide limit of cursors per session is set by the initialization
> parameter named OPEN_CURSORS found in the parameter file (such as
> INIT.ORA).
>
> Explicitly creating cursors for precompiler programs can offer some
> advantages in tuning those applications. For example, increasing the
> number of cursors can often reduce the frequency of parsing and improve
> performance. If you know how many cursors may be required at a given
> time, then you can make sure you can open that many simultaneously.
>
> After each stage of execution, the cursor retains enough information
> about the SQL statement to re-execute the statement without starting
> over, as long as no other SQL statement has been associated with that
> cursor. Note that the statement can be re-executed without including
> the parse stage.
>
> By opening several cursors, the parsed representation of several SQL
> statements can be saved. Repeated execution of the same SQL statements
> can thus begin at the describe, define, bind, or execute step, saving
> the repeated cost of opening cursors and parsing.
>
> Closing a cursor means that the information currently in the associated
> private area is lost and its memory is deallocated. Once a cursor is
> opened, it is not closed until one of the following events occurs:
>
> The user program terminates its connection to the server.
>
> If the user program is an OCI program or precompiler application, then
> it explicitly closes any open cursor during the execution of that
> program. (However, when this program terminates, any cursors remaining
> open are implicitly closed.)
>
> My advice? Increase the open_cursors parameter.
>
> --
> David Fitzjarrell
> Oracle Certified DBA
>
Received on Thu Apr 05 2001 - 09:00:21 CDT