RE: Monitoring DB cursor leaks

Hi,

You could write your own ConnectionFactory  
http://db.apache.org/ojb/docu/guides/connection.html
And execute the appropriate statement in the lookup method.

E.g.

public class MyConnectionManager extends ConnectionFactoryDBCPImpl {
   public Connection lookupConnection(JdbcConnectionDescriptor jcd)
throws LookupException {
      Connection con = super.lookupConnection(jcd);
  	if(con != null){
		/* Execute the ALTER stataement here */
	}
   };
}

This would be executed every time a new connection was requested for the
Broker; this is probably overkill, but it is simple.

The more "correct" way of doing it would be to execute the SQL statement
when the connection is actually *created* - but where that is, depends
on which connection factory you are using.

Hope that's a pointer in the right direction for you...

Cheers,

Charles.


> -----Original Message-----
> From: Bruno CROS [mailto:[email protected]] 
> Sent: 10 October 2006 09:34
> To: OJB Users List
> Subject: Re: Monitoring DB cursor leaks
> 
> http://oracleandy.blogspot.com/2006/03/vopencursor-find-cursor
> s-that-are.html
> 
> Perhaps i found something to detect bad code, but i don't 
> known how to set the session parameter in OJB connections. 
> The equivalent of :
> alter session set "_close_cached_open_cursors" = TRUE
> 
>  Did someone already do ?
> 
> Thanks
> 
> 
> 
> 
> On 10/10/06, Bruno CROS <[email protected]> wrote:
> >
> >   Hi,
> >
> > Here is my connector, Oracle9i yet...
> >
> > May be i have to set the jdbc-level at 3.0 ?
> >
> > Referring to this article, it seems that it is a hard to 
> resolve trouble.
> >
> > http://www.orafaq.com/node/758
> >
> > Except looking into code, how to check "real" cursors leak 
> (if  they are)?
> >
> > Regards
> >
> > <jdbc-connection-descriptor
> >                 jcd-alias="default"
> >                 default-connection="true"
> >                 platform="Oracle9i"
> >                 jdbc-level=" 2.0"
> >                 driver="oracle.jdbc.driver.OracleDriver"
> >                 protocol="jdbc"
> >                 subprotocol="oracle"
> >                 dbalias="thin:@XXXX:1521:ZZZZZ"
> >                 username="uuuuuuuuu"
> >                 password="pppppppp"
> >                 batch-mode="false"
> >         useAutoCommit="1"
> >         ignoreAutoCommitExceptions="false"
> >      >
> >
> >
> >
> >    ---------- Forwarded message ----------
> > From: Danilo Tommasina <[email protected] >
> > Date: Oct 9, 2006 6:08 PM
> > Subject: Re: Monitoring DB cursor leaks
> > To: OJB Users List <[email protected]>
> >
> > Hi,
> >
> > we were having that problem too, long time ago. You should use the 
> > 'Oracle9i' Platform setting in your 
> jdbc-connection-descriptor instead 
> > of the Oracle 'Platform', that should fix the problem. IIRC the 
> > 'Oracle' Platform does a caching of PreparedStatements, however the 
> > Oracle Driver has its own PreparedStatements cache, having 
> this double 
> > caching causes too many cursors to stay open.
> > The Oracle9i Platform should not make use of the local 
> > PreparedStatements cache.
> > Furhtermore the 'Oracle9i' offers better performance and correct 
> > handling for BLOBs > 4kB, for 9i or later Oracle versions.
> >
> > Good luck
> > cheers
> > Danilo
> >
> > Bruno CROS wrote:
> > >  Hi all,
> > >
> > >  Experiencing some MAX OPEN CURSOR oracle errors (ORA 
> -01000) , i'm
> > looking
> > > for the cursors leaks ( unclosed ResultSet it seems) in code.
> > >
> > > I guess that broker queries well close the result set. So when 
> > > report queries results iteration are closed ? on 
> HasNext()==false ?
> > >
> > > Is it possible to check open cursors when releasing connections ? 
> > > and by
> >
> > > the
> > > way, close them if needed ?
> > >
> > > Note : MAX_OPEN_CURSOR Oracle parameter is actually set at 500 by 
> > > connection ( and 1000 sooner )
> > >
> > > Oracle 10, OJB 1.0.4 with some litle updates
> > >
> > >  Regards.
> > >
> >
> > 
> ---------------------------------------------------------------------
> > To unsubscribe, e-mail: [email protected]
> > For additional commands, e-mail: [email protected]
> >
> >
> >
>