File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes open cursors monitoring help required Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "open cursors monitoring help required" Watch "open cursors monitoring help required" New topic

open cursors monitoring help required

waseem aslam

Joined: Mar 09, 2006
Posts: 1
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,

best regards,
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
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...
Reid M. Pinchback
Ranch Hand

Joined: Jan 25, 2002
Posts: 775
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 unit testing.

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 ]

Reid - SCJP2 (April 2002)
I agree. Here's the link:
subject: open cursors monitoring help required
It's not a secret anymore!