Check up the V$Open_Cursor view for finding out the errant SQL,
select SQL_TEXT,count(*),USER_NAME from
V$OPEN_CURSOR group by SQL_TEXT,USER_NAME
order by count(*);
> ----------
> From: Bill Burke
> Reply To: JBoss-User
> Sent: Tuesday, March 6, 2001 10:15 AM
> To: JBoss-User
> Subject: Re: [jBoss-User] maximum open cursors exceeded
>
> Where does that get me? Shouldn't the number of open cursors get
> cleaned up if I'm closing the ResultSet, Statement, and Connection?
>
> Regards,
> Bill
>
> Guy Rouillier wrote:
>
> > Have you tried increasing the number of open cursors allowed?
> >
> > ----- Original Message -----
> > From: "Bill Burke" <[EMAIL PROTECTED]>
> > To: "JBoss-User" <[EMAIL PROTECTED]>
> > Sent: Monday, March 05, 2001 10:52 PM
> > Subject: Re: [jBoss-User] maximum open cursors exceeded
> >
> >
> >> I tried closing the ResultSet before the Statement and still, after
> >> awhile, I get maximum open cursors exceeded. Anybody else run into
> this
> >> problem with regular EJBs?
> >>
> >> Thanks,
> >> Bill
> >>
> >> Bill Burke wrote:
> >>
> >>> I don't know if this is wrong to do, but I'm using an Oracle
> >>> connection pool that I created for my EJB's to execute SQL queries and
>
> >>> updates. The problem is, after awhile I get a "maximum open cursors
> >>> exceeded" error from Oracle. I'm running on RedHat linux 6.2 with an
> >>> Oracle DB running on NT. I'm using the jdbc:oracle:thin drivers.
> >>>
> >>> jboss.jcml:
> >>> <mbean name="DefaultDomain:service=XADataSource,name=oraclePool">
> >>> ....
> >>> </mbean>
> >>>
> >>> Java code is below.
> >>> Some things to note, executeSQLUpdate and executeSQLQuery. I can't
> >>> seem to find out where I'm not cleaning up connections. Do you have
> >>> to close the ResultSet? The JDBC docs say that if you close the
> >>> Statement, then your result sets will be closed as well.
> >>>
> >>> Thanks in advance,
> >>> Bill
> >>>
> >>> Java Code:
> >>>
> >>>
> >>> import java.io.*;
> >>> import java.rmi.*;
> >>> import javax.ejb.*;
> >>> import java.util.*;
> >>> import javax.naming.*;
> >>> import com.ctone.services.utils.*;
> >>> import com.ctone.services.constants.*;
> >>> import com.ctone.services.framework.*;
> >>> import javax.sql.*;
> >>> import java.sql.*;
> >>>
> >>> public class CTSQLServiceBean extends CTServiceBean
> >>> {
> >>> public CTSQLServiceBean()
> >>> {
> >>> super();
> >>> }
> >>>
> >>> private Connection getConnection() throws Exception
> >>> {
> >>> javax.sql.DataSource source =
> >>> (javax.sql.DataSource)getInitialContext().lookup("java:/oraclePool");
> >>> return source.getConnection();
> >>> }
> >>>
> >>> public int executeSQLUpdate(String sqlUpdate) throws RemoteException
> >>> {
> >>> Connection connect = null;
> >>> Statement statement = null;
> >>> int rowCount = 0;
> >>>
> >>> try
> >>> {
> >>> connect = getConnection();
> >>> if (connect == null)
> >>> {
> >>> ServicesLog.logAndThrow( CTExceptionType.RECOVERABLE,
> >>> "Unable to connect to database." );
> >>> }
> >>> ServicesLog.log( 5, "Got connection, creating statement..." );
> >>>
> >>> statement = connect.createStatement();
> >>> if (statement == null)
> >>> {
> >>> ServicesLog.logAndThrow( CTExceptionType.RECOVERABLE,
> >>> "Unable to create SQL statement for: '" + sqlUpdate
> >>> + "'" );
> >>> }
> >>> ServicesLog.log( 5, "Created statement, executing query..." );
> >>>
> >>> rowCount = statement.executeUpdate( sqlUpdate );
> >>> if (rowCount == 0)
> >>> {
> >>> ServicesLog.log( 5, "No rows affected by SQL statement '" +
> >>> sqlUpdate + "'" );
> >>> }
> >>> else
> >>> {
> >>> ServicesLog.log( 5, String.valueOf( rowCount ) + " rows affected
> >>> by SQL statement..." );
> >>> }
> >>> }
> >>> catch (SQLException sqe)
> >>> {
> >>> ServicesLog.logAndThrow( CTExceptionType.RECOVERABLE,
> >>> "Unable to execute SQL query: '" + sqlUpdate + "'",
> >>> sqe );
> >>> }
> >>> catch (Exception ex)
> >>> {
> >>> ServicesLog.logAndThrow( CTExceptionType.RECOVERABLE,
> >>> "Unable to get connection pool Driver.",
> >>> ex );
> >>> }
> >>> finally
> >>> {
> >>> if (statement != null)
> >>> {
> >>> try
> >>> {
> >>> statement.close();
> >>> }
> >>> catch (SQLException sqe)
> >>> {
> >>> }
> >>> }
> >>>
>
> >>> if (connect != null)
> >>> {
> >>> try
> >>> {
> >>> connect.close();
> >>> }
> >>> catch (SQLException sqe)
> >>> {
> >>> }
> >>> }
> >>> }
> >>>
> >>> return rowCount;
> >>> }
> >>>
> >>> public CTResultSet executeSQLQuery(String sqlQuery) throws
> >>> RemoteException
> >>> {
> >>> Connection connect = null;
> >>> Statement statement = null;
> >>> CTResultSet results = null;
> >>>
> >>> try
> >>> {
> >>> connect = getConnection();
> >>> if (connect == null)
> >>> {
> >>> ServicesLog.logAndThrow( CTExceptionType.RECOVERABLE,
> >>> "Unable to connect to database." );
> >>> }
> >>> ServicesLog.log( 5, "Got connection, creating statement..." );
> >>>
> >>> statement = connect.createStatement();
> >>> if (statement == null)
> >>> {
> >>> ServicesLog.logAndThrow( CTExceptionType.RECOVERABLE,
> >>> "Unable to create SQL statement for query: '" +
> >>> sqlQuery + "'" );
> >>> }
> >>> ServicesLog.log( 5, "Created statement, executing query..." );
> >>>
> >>> ResultSet resultSet = statement.executeQuery( sqlQuery );
> >>> if (resultSet == null)
> >>> {
> >>> ServicesLog.log( 5, "No results returned for query '" + sqlQuery
> >>> + "'" );
> >>> }
> >>> else
> >>> {
> >>> ServicesLog.log( 5, "Got results from query..." );
> >>> results = new CTResultSet( resultSet );
> >>> }
> >>> }
> >>> catch (SQLException sqe)
> >>> {
> >>> ServicesLog.logAndThrow( CTExceptionType.RECOVERABLE,
> >>> "Unable to execute SQL query: '" + sqlQuery + "'",
> >>> sqe );
> >>> }
> >>> catch (Exception ex)
> >>> {
> >>> ServicesLog.logAndThrow( CTExceptionType.RECOVERABLE,
> >>> "Unable to get connection pool Driver.",
> >>> ex );
> >>> }
> >>> finally
> >>> {
> >>> if (statement != null)
> >>> {
> >>> try
> >>> {
> >>> statement.close();
> >>> }
> >>> catch (SQLException sqe)
> >>> {
> >>> }
> >>> }
> >>>
> >>> if (connect != null)
> >>> {
> >>> try
> >>> {
> >>> connect.close();
> >>> }
> >>> catch (SQLException sqe)
> >>> {
> >>> }
> >>> }
> >>> }
> >>>
> >>> return results;
> >>> }
> >>> }
> >>>
> >>>
> >>>
> >>>
> >>> --
> >>> --------------------------------------------------------------
> >>> To subscribe: [EMAIL PROTECTED]
> >>> To unsubscribe: [EMAIL PROTECTED]
> >>>
> >>>
> >>>
> >>>
> >>
> >>
> >>
> >> --
> >> --------------------------------------------------------------
> >> To subscribe: [EMAIL PROTECTED]
> >> To unsubscribe: [EMAIL PROTECTED]
> >>
> >>
> >
> >
> >
> > --
> > --------------------------------------------------------------
> > To subscribe: [EMAIL PROTECTED]
> > To unsubscribe: [EMAIL PROTECTED]
> >
> >
> >
> >
>
>
>
>
> --
> --------------------------------------------------------------
> To subscribe: [EMAIL PROTECTED]
> To unsubscribe: [EMAIL PROTECTED]
>
--
--------------------------------------------------------------
To subscribe: [EMAIL PROTECTED]
To unsubscribe: [EMAIL PROTECTED]
