I am using resin2.1.x connection pooling and oracle9i, i want to know is it possible to remove a certain connection from the pool manually. Infact i will observer if a certain connection is using more than 100 cursors, then just remove that connection pool, and get some new. it will be use ful to prevent MAX OPEN CURSOR EXCEEDED, i am also interested to know how to track, how many cursors are involved with a certain connection,
On the database side, if you have the necessary privileges, you can see the number of open cursors per connection with: select a.sid, a.serial#, a.username, b.cursor_count from v$session a, (select sid, count(*) cursor_count from v$open_cursor group by sid) b where a.sid = b.sid
(The v$ views are actually mostly internal Oracle memory sturctures rather than tables; data consistency is guaranteed by locking memory briefly, so great care should be taken in designing SQL that will query them. A complex, poorly written query can relatively rarely bring performance to an absolute crawl).
I don't know how you could tie this information to the connection objects in the Java world though...
Not familiar with Resin specifics, but most J2EE containers have JMX services that have info like this. You may want to look to the Resin docs for anything that sounds JMX/MBean-ish for similar info. If not, you can always write your own JDBC driver implementation that proxies around the real driver to install your own tracking. I had to do that once for a hideous legacy webapp that was leaking connections and it isn't that hard. Basically the same thing as creating mock objects for unittesting.
Exercise the app, find the places that are leaking result sets, fix them, and go back to the original driver. Much more effective than trying to hack active connections, unless you are trying to be some form of hosting site and the problem is from code you can't fix. [ March 09, 2006: Message edited by: Reid M. Pinchback ]