Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: ORA-01000-Maximum open cursors exceeded

Re: ORA-01000-Maximum open cursors exceeded

From: <y.abiad_at_waika9.com>
Date: Fri, 6 Apr 2001 23:35:24 +0200
Message-ID: <9alcl5$2e$1@fenris.isp.9tel.net>

Hello,
Is your application use java 1.1.8 via jdbc light to connect your database ? I have the same pb. and i only set OPEN_CACHE_CURSOR=true in the init.ora to solve the pb.
The open_cursor(=150) parameter is per user.

"Dave Weeks" <dweeks_at_nospam.gnseurope.com> a écrit dans le message news: 9ahuht$sck$1_at_reader-00.news.insnet.cw.net...
> 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 Fri Apr 06 2001 - 16:35:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US