Ryan,
I had the exact same configuration (Oracle 8i/JBoss 3) and problem with the
cursors. I found an article on the Oracle site that explained the whole
deal. Turned out I wasn't closing the result sets.
Tim
ORA-01000: maximum open cursors exceeded
"I suspect that statement.close() leaves DB Cursors open. ... After doing a
lot of database fetches in JDBC, I eventually get the following error:"
ORA-01000: maximum open cursors exceeded
* The error ORA-01000: maximum open cursors exceeded happens when not all
of the statement and result set objects are closed. In almost all cases when
this error occurs, the programmer discovers that he or she has inadvertently
missed the close() methods. Please make sure that all your statement, result
set, and connection objects are explicitly closed after you have finished
processing to avoid this error. The following is a snippet for such code:
try { ...
} finally {
try { rs.close(); rs=null;
stmt.close(); stmt=null;
} catch (Exception e){}
}
* Note that if -for other reasons- you need to increase the maximum number
of cursors, you can do so in the initxxx.ora by specifying (assuming the
default is 50 or so)
open_cursors=200
* You should consider using SQLJ from the start. SQLJ will automatically
manage statement objects (though you still must take care to close your
result sets and connection contexts). Then you will not have to look at code
such as the following from the Oracle 8.1.5 Application Developer's Guide -
Large Objects (LOBs):
OracleCallableStatement cstmt = (OracleCallableStatement)
conn.prepareCall
("BEGIN DBMS_LOB.OPEN( ?, DBMS_LOB.LOB_READWRITE); END;");
cstmt.setCLOB(1, lob_loc);
cstmt.execute();
...
cstmt = (OracleCallableStatement) conn.prepareCall
("BEGIN DBMS_LOB.CLOSE(?); END;");
cstmt.setCLOB(1, lob_loc);
cstmt.execute();
cstmt.close();
The above code opens two cursors with the cstmt but ends up closing only one.
In SQLJ you would have written much more correctly and concisely:
#sql { BEGIN DBMS_LOB.OPEN(:lob_loc, DBMS_LOB.LOB_READWRITE); END; };
...
#sql { BEGIN DBMS_LOB.CLOSE(:lob_loc); END; };
Or, consider the following subtly buggy code:
switch (mode)
{
case READ_ONLY :
dbStatement =
conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
case READ_WRITE :
dbStatement =
conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
}
Notice the missing break statement which causes the leaking of cursors.
* You also need to make sure to close all of the SQLJ connection context
objects, rather than waiting for them to be garbage-collected. SQLJ 8.1.6 and
later performs automatic caching of the last five SQLJ statements that have
been executed. Whenever you close the SQLJ connection context you ensure that
the statements cached on it are being closed as well. You can use the method
close(sqlj.runtime.ConnectionContext.KEEP_CONNECTION) if you just want to
clean up the SQLJ statement cache but not close the underlying JDBC
connection.
Cleaning up the SQLJ connection context is particularly important in
server-side code, since closing the server-side default connection is
performed as a no-op and will not result in any statement (or cursor)
cleanup. Alternatively, you may just want to use a single static SQLJ
connection context, such as the default context, in your server-side code.
* A final tip: In order to find the bad cursors, debug/break in the
exception catcher, and then look at all opened cursors in TOP SESSIONS. In
every case of "max cursors exceeded", the same cursor was present hundreds of
times, and easily found.
On Monday 17 February 2003 03:41 pm, Sonnek, Ryan wrote:
> thank you all for your reply, i'll try and clear some things up for all
> those that responded.
>
> mark:
> sorry if i sounded demanding. i am more than willing to contribute my
> knowledge (however limited) to help the jboss project continue to thrive.
> i've been an avid user of jboss for the past 2 years now, and just recently
> i've begun to dig into the actual code. that being said, i still consider
> myself a relative rookie, and my post was meant to ask advice, not demand
> service. again, sorry if my netiquete came off rude.
>
> david, igor:
> i'm using BMP beans and all of my database connections and prepared
> statements are being closed in finally blocks. my resulset's are not being
> closed at all, but isn't it stated somewhere that closing the statement,
> closes all resultsets? i will test if closing resultset's makes any
> difference.
>
> bill, luke:
> thank you for your ideas, i will be checking the oracle tables you
> mentioned to see if i can get some more information.
>
> thank you all again for your replies!
> Ryan
>
> -----Original Message-----
> From: Igor Fedorenko [mailto:[EMAIL PROTECTED]]
> Sent: Monday, February 17, 2003 12:56 PM
> To: [EMAIL PROTECTED]
> Subject: RE: [JBoss-dev] jbosscx rfe 677512
>
> > -----Original Message-----
> > From: Sonnek, Ryan [mailto:[EMAIL PROTECTED]]
> > Sent: Monday, February 17, 2003 11:50 AM
> > To: '[EMAIL PROTECTED]'
> > Subject: [JBoss-dev] jbosscx rfe 677512
> >
> >
> > i posted this request about a week ago, and would like to know any
> > developers thoughts on this. to summerize, i'd like to be
> > able to force a
> > datasource to timeout any connections in the pool after a
> > period of time.
> >
> > we're running jboss 3.0.6 with oracle 8i and right now and
> > we're constantly
> > running into an error "Maximum number of cursors exceeded."
> > being able to
> > timeout connections that have been used heavily to restore
> > those cursors is
> > the only idea i had to get around this error.
> >
> > unfortunately this is a MAJOR problem for us right now, since
> > we can't get
> > our application up with 24/7 reliability. it only takes a
> > matter of minutes
> > of heavy activity to exceed 300 cursors on oracle. if there
> > is some other
> > way to get around this error, i would appreciate any
> > feedback. or, if this
> > is a planned enhancement, when would it be integrated?
>
> I am not sure I understand your problem. In RFE you are saying "cached
> prepared statements stay alive and cause this error". What caches prepared
> statements? Did you explicitly enabled statement caching on oracle
> connections?
>
> I remember seeing this error message but it turned to be a problem in one
> of my SSBs which did not explicitly close java.sql.[Prepared]Statement.
> Well, strictly speaking it is a known JBoss bug/limitation --
> "Connection.close()" does not close all opened [Prepared]Statement as it is
> supposed to -- but is has a reasonable workaround.
>
>
> -------------------------------------------------------
> This sf.net email is sponsored by:ThinkGeek
> Welcome to geek heaven.
> http://thinkgeek.com/sf
> _______________________________________________
> Jboss-development mailing list
> [EMAIL PROTECTED]
> https://lists.sourceforge.net/lists/listinfo/jboss-development
>
>
> -------------------------------------------------------
> This sf.net email is sponsored by:ThinkGeek
> Welcome to geek heaven.
> http://thinkgeek.com/sf
> _______________________________________________
> Jboss-development mailing list
> [EMAIL PROTECTED]
> https://lists.sourceforge.net/lists/listinfo/jboss-development
-------------------------------------------------------
This sf.net email is sponsored by:ThinkGeek
Welcome to geek heaven.
http://thinkgeek.com/sf
_______________________________________________
Jboss-development mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-development
