You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by Jason McKerr <mc...@nacse.org> on 2003/05/07 18:05:51 UTC
Re: Oracle "too many open cursors"
Chris,
Can you give us the actual ORA message number that your getting.
Also, in your Oracle init.ora file, you might want to increase the
number of allowable cursors. In Oracle 8i the default is 64. I think
my DBA's usually up that to a larger number.
In the init.ora file the variable is called "open_cursors." You just
increase that value.
Jason
On Wed, 2003-05-07 at 06:54, Chris Lewington wrote:
> Hi all,
>
> Anyone had problems with "too many open cursors" using oracle? Tried all
> the usual things like closing statements etc via RsIterator (although I
> typically exhaust the iterator anyway so the statements should be closed
> automatically), also switched off connection pooling (both at OJB and
> oracle driver level), also even tried eager-release="true" although not
> using an app server (worked better, but still not enough). I don't
> understand oracle cursors too well, so I'm not sure whether my problem
> is application or DB server side - though I have read that oracle does
> not always release cursors on a statment.close() call, and the drivers
> can do some odd things with statement cacheing. As far as I can tell
> there is little else I can do application wise - I don't want to close
> the physical connection each time as I have a web environment and want
> the connection for a session, but statment.close() doesn't seem to work
> for me. Any ideas/oracle experts using OJB that could help?
>
> My setup: OJB 0.9.7 (will upgrade as needed, but currently this is OK
> for us - or is the above addressed in later releases?), tomcat/struts,
> oracle 8.1.6.2 driver, JDK 1.4.
>
> Cheers,
>
> Chris
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Oracle "too many open cursors"
Posted by Jason McKerr <mc...@nacse.org>.
Yeah maybe. I didn't have a cursor problem, but I did run into
pooling/session/processes problem, but it might amount to a similar
thing (I've tested mostly on Orion/Jrun/Resin). They went away when I
upgraded.
You also need to make sure that you are properly using close() for
closing your broker connections. The most common problem in ORA-1000
are things left open. Are you using close in, say a finally block?
Maybe see if there are exceptions getting thrown while/before closing.
600 is a lot of cursors, and unless other apps are cranking on this DB
server, you should never hit that. You can check the table
v$open_cursor to see if lots of them are open at runtime.
You might also consider trying the Oracle specific pooling
implementation class. I'm not sure how to do that in tomcat, but I
found that OJB doesn't always like the Pooling implementations on some
J2EE servers (my problems were with Orion/Oracle). The Oracle Pooling
DataSource class: "oracle.jdbc.pool.OracleConnectionPoolDataSource"
Jason
On Thu, 2003-05-08 at 09:13, Chris Lewington wrote:
> Hi Jason,
>
> Tomcat, and oracle thin driver (same behaviour with the plain vanilla one as well
> as the "built-in connection pool" driver). Did the problem go away switching to eg
> OJB 1?
>
> Chris
>
> Jason McKerr wrote:
>
> > You're problem may be the OJB version that you are using. I had similar
> > problems with earlier versions.
> >
> > Are you on Tomcat or JRun? If JRun are you using the JRun/Merant Oracle
> > driver or the Oracle thin driver?
> >
> > Jason
> >
> > On Thu, 2003-05-08 at 03:25, Chris Lewington wrote:
> > > Jason,
> > >
> > > Yep, the actual error is "ORA-01000: maximum open cursors exceeded".
> > >
> > > I believe we have the "open_cursors" variable in init.ora set to about 600,
> > > but I'd always thought that increasing this would be something of a "quick
> > > fix" and not address the real problem. I've seen others recommend a figure of
> > > 1500 for eg JRun environments. I also believe that oracle generates more than
> > > the number of open statements and you have no control over these - statement
> > > cacheing by the drivers makes things even worse..........
> > >
> > > Is it possible to get a PB instance to dump its connection if it is unusable
> > > and re-acquire another? (I'm assuming no connection pooling here, as closing
> > > one would only return it to the pool whereas I would need a fresh physical
> > > one). Or is there a connection pool that anyone knows of which tests
> > > connections before returning a "good" one?
> > >
> > > Cheers,
> > >
> > > Chris
> > >
> > > Jason McKerr wrote:
> > >
> > > > Chris,
> > > >
> > > > Can you give us the actual ORA message number that your getting.
> > > >
> > > > Also, in your Oracle init.ora file, you might want to increase the
> > > > number of allowable cursors. In Oracle 8i the default is 64. I think
> > > > my DBA's usually up that to a larger number.
> > > >
> > > > In the init.ora file the variable is called "open_cursors." You just
> > > > increase that value.
> > > >
> > > > Jason
> > > >
> > > > On Wed, 2003-05-07 at 06:54, Chris Lewington wrote:
> > > > > Hi all,
> > > > >
> > > > > Anyone had problems with "too many open cursors" using oracle? Tried all
> > > > > the usual things like closing statements etc via RsIterator (although I
> > > > > typically exhaust the iterator anyway so the statements should be closed
> > > > > automatically), also switched off connection pooling (both at OJB and
> > > > > oracle driver level), also even tried eager-release="true" although not
> > > > > using an app server (worked better, but still not enough). I don't
> > > > > understand oracle cursors too well, so I'm not sure whether my problem
> > > > > is application or DB server side - though I have read that oracle does
> > > > > not always release cursors on a statment.close() call, and the drivers
> > > > > can do some odd things with statement cacheing. As far as I can tell
> > > > > there is little else I can do application wise - I don't want to close
> > > > > the physical connection each time as I have a web environment and want
> > > > > the connection for a session, but statment.close() doesn't seem to work
> > > > > for me. Any ideas/oracle experts using OJB that could help?
> > > > >
> > > > > My setup: OJB 0.9.7 (will upgrade as needed, but currently this is OK
> > > > > for us - or is the above addressed in later releases?), tomcat/struts,
> > > > > oracle 8.1.6.2 driver, JDK 1.4.
> > > > >
> > > > > Cheers,
> > > > >
> > > > > Chris
> > > > >
> > > > >
> > > > > ---------------------------------------------------------------------
> > > > > To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> > > > > For additional commands, e-mail: ojb-user-help@db.apache.org
> > > >
> > > > ---------------------------------------------------------------------
> > > > To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> > > > For additional commands, e-mail: ojb-user-help@db.apache.org
> > >
> > >
> > > ---------------------------------------------------------------------
> > > To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> > > For additional commands, e-mail: ojb-user-help@db.apache.org
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> > For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Oracle "too many open cursors"
Posted by Chris Lewington <cl...@ebi.ac.uk>.
Hi Jason,
Tomcat, and oracle thin driver (same behaviour with the plain vanilla one as well
as the "built-in connection pool" driver). Did the problem go away switching to eg
OJB 1?
Chris
Jason McKerr wrote:
> You're problem may be the OJB version that you are using. I had similar
> problems with earlier versions.
>
> Are you on Tomcat or JRun? If JRun are you using the JRun/Merant Oracle
> driver or the Oracle thin driver?
>
> Jason
>
> On Thu, 2003-05-08 at 03:25, Chris Lewington wrote:
> > Jason,
> >
> > Yep, the actual error is "ORA-01000: maximum open cursors exceeded".
> >
> > I believe we have the "open_cursors" variable in init.ora set to about 600,
> > but I'd always thought that increasing this would be something of a "quick
> > fix" and not address the real problem. I've seen others recommend a figure of
> > 1500 for eg JRun environments. I also believe that oracle generates more than
> > the number of open statements and you have no control over these - statement
> > cacheing by the drivers makes things even worse..........
> >
> > Is it possible to get a PB instance to dump its connection if it is unusable
> > and re-acquire another? (I'm assuming no connection pooling here, as closing
> > one would only return it to the pool whereas I would need a fresh physical
> > one). Or is there a connection pool that anyone knows of which tests
> > connections before returning a "good" one?
> >
> > Cheers,
> >
> > Chris
> >
> > Jason McKerr wrote:
> >
> > > Chris,
> > >
> > > Can you give us the actual ORA message number that your getting.
> > >
> > > Also, in your Oracle init.ora file, you might want to increase the
> > > number of allowable cursors. In Oracle 8i the default is 64. I think
> > > my DBA's usually up that to a larger number.
> > >
> > > In the init.ora file the variable is called "open_cursors." You just
> > > increase that value.
> > >
> > > Jason
> > >
> > > On Wed, 2003-05-07 at 06:54, Chris Lewington wrote:
> > > > Hi all,
> > > >
> > > > Anyone had problems with "too many open cursors" using oracle? Tried all
> > > > the usual things like closing statements etc via RsIterator (although I
> > > > typically exhaust the iterator anyway so the statements should be closed
> > > > automatically), also switched off connection pooling (both at OJB and
> > > > oracle driver level), also even tried eager-release="true" although not
> > > > using an app server (worked better, but still not enough). I don't
> > > > understand oracle cursors too well, so I'm not sure whether my problem
> > > > is application or DB server side - though I have read that oracle does
> > > > not always release cursors on a statment.close() call, and the drivers
> > > > can do some odd things with statement cacheing. As far as I can tell
> > > > there is little else I can do application wise - I don't want to close
> > > > the physical connection each time as I have a web environment and want
> > > > the connection for a session, but statment.close() doesn't seem to work
> > > > for me. Any ideas/oracle experts using OJB that could help?
> > > >
> > > > My setup: OJB 0.9.7 (will upgrade as needed, but currently this is OK
> > > > for us - or is the above addressed in later releases?), tomcat/struts,
> > > > oracle 8.1.6.2 driver, JDK 1.4.
> > > >
> > > > Cheers,
> > > >
> > > > Chris
> > > >
> > > >
> > > > ---------------------------------------------------------------------
> > > > To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> > > > For additional commands, e-mail: ojb-user-help@db.apache.org
> > >
> > > ---------------------------------------------------------------------
> > > To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> > > For additional commands, e-mail: ojb-user-help@db.apache.org
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> > For additional commands, e-mail: ojb-user-help@db.apache.org
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Oracle "too many open cursors"
Posted by Jason McKerr <mc...@nacse.org>.
You're problem may be the OJB version that you are using. I had similar
problems with earlier versions.
Are you on Tomcat or JRun? If JRun are you using the JRun/Merant Oracle
driver or the Oracle thin driver?
Jason
On Thu, 2003-05-08 at 03:25, Chris Lewington wrote:
> Jason,
>
> Yep, the actual error is "ORA-01000: maximum open cursors exceeded".
>
> I believe we have the "open_cursors" variable in init.ora set to about 600,
> but I'd always thought that increasing this would be something of a "quick
> fix" and not address the real problem. I've seen others recommend a figure of
> 1500 for eg JRun environments. I also believe that oracle generates more than
> the number of open statements and you have no control over these - statement
> cacheing by the drivers makes things even worse..........
>
> Is it possible to get a PB instance to dump its connection if it is unusable
> and re-acquire another? (I'm assuming no connection pooling here, as closing
> one would only return it to the pool whereas I would need a fresh physical
> one). Or is there a connection pool that anyone knows of which tests
> connections before returning a "good" one?
>
> Cheers,
>
> Chris
>
> Jason McKerr wrote:
>
> > Chris,
> >
> > Can you give us the actual ORA message number that your getting.
> >
> > Also, in your Oracle init.ora file, you might want to increase the
> > number of allowable cursors. In Oracle 8i the default is 64. I think
> > my DBA's usually up that to a larger number.
> >
> > In the init.ora file the variable is called "open_cursors." You just
> > increase that value.
> >
> > Jason
> >
> > On Wed, 2003-05-07 at 06:54, Chris Lewington wrote:
> > > Hi all,
> > >
> > > Anyone had problems with "too many open cursors" using oracle? Tried all
> > > the usual things like closing statements etc via RsIterator (although I
> > > typically exhaust the iterator anyway so the statements should be closed
> > > automatically), also switched off connection pooling (both at OJB and
> > > oracle driver level), also even tried eager-release="true" although not
> > > using an app server (worked better, but still not enough). I don't
> > > understand oracle cursors too well, so I'm not sure whether my problem
> > > is application or DB server side - though I have read that oracle does
> > > not always release cursors on a statment.close() call, and the drivers
> > > can do some odd things with statement cacheing. As far as I can tell
> > > there is little else I can do application wise - I don't want to close
> > > the physical connection each time as I have a web environment and want
> > > the connection for a session, but statment.close() doesn't seem to work
> > > for me. Any ideas/oracle experts using OJB that could help?
> > >
> > > My setup: OJB 0.9.7 (will upgrade as needed, but currently this is OK
> > > for us - or is the above addressed in later releases?), tomcat/struts,
> > > oracle 8.1.6.2 driver, JDK 1.4.
> > >
> > > Cheers,
> > >
> > > Chris
> > >
> > >
> > > ---------------------------------------------------------------------
> > > To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> > > For additional commands, e-mail: ojb-user-help@db.apache.org
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> > For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Oracle "too many open cursors"
Posted by Chris Lewington <cl...@ebi.ac.uk>.
Jason,
Yep, the actual error is "ORA-01000: maximum open cursors exceeded".
I believe we have the "open_cursors" variable in init.ora set to about 600,
but I'd always thought that increasing this would be something of a "quick
fix" and not address the real problem. I've seen others recommend a figure of
1500 for eg JRun environments. I also believe that oracle generates more than
the number of open statements and you have no control over these - statement
cacheing by the drivers makes things even worse..........
Is it possible to get a PB instance to dump its connection if it is unusable
and re-acquire another? (I'm assuming no connection pooling here, as closing
one would only return it to the pool whereas I would need a fresh physical
one). Or is there a connection pool that anyone knows of which tests
connections before returning a "good" one?
Cheers,
Chris
Jason McKerr wrote:
> Chris,
>
> Can you give us the actual ORA message number that your getting.
>
> Also, in your Oracle init.ora file, you might want to increase the
> number of allowable cursors. In Oracle 8i the default is 64. I think
> my DBA's usually up that to a larger number.
>
> In the init.ora file the variable is called "open_cursors." You just
> increase that value.
>
> Jason
>
> On Wed, 2003-05-07 at 06:54, Chris Lewington wrote:
> > Hi all,
> >
> > Anyone had problems with "too many open cursors" using oracle? Tried all
> > the usual things like closing statements etc via RsIterator (although I
> > typically exhaust the iterator anyway so the statements should be closed
> > automatically), also switched off connection pooling (both at OJB and
> > oracle driver level), also even tried eager-release="true" although not
> > using an app server (worked better, but still not enough). I don't
> > understand oracle cursors too well, so I'm not sure whether my problem
> > is application or DB server side - though I have read that oracle does
> > not always release cursors on a statment.close() call, and the drivers
> > can do some odd things with statement cacheing. As far as I can tell
> > there is little else I can do application wise - I don't want to close
> > the physical connection each time as I have a web environment and want
> > the connection for a session, but statment.close() doesn't seem to work
> > for me. Any ideas/oracle experts using OJB that could help?
> >
> > My setup: OJB 0.9.7 (will upgrade as needed, but currently this is OK
> > for us - or is the above addressed in later releases?), tomcat/struts,
> > oracle 8.1.6.2 driver, JDK 1.4.
> >
> > Cheers,
> >
> > Chris
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> > For additional commands, e-mail: ojb-user-help@db.apache.org
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org