JDBC: How to determine connection SessionID and serialNumber

Hi Folks,

I'm working on a java app right now that uses the Oracle Thin JDBC Driver (10.2.0.3.0) to communicate with a back end Oracle server (10.2.0.3.0).

Everything is working fine, but I've got a new requirement that an administrator be able to "kill" a user's database activity if the user in question did something unwise like, say, a freeform query that table scanned a log table or some such and browned out the database.

Its fairly easy for a DBA to find the offending session and kill it from sqlplus or OEM and that's what we usually do.

The new requirement though is that the application admin should be able to do this from within the app itself by identifying the transaction (within the app) and pressing the kill button.

Within the app I have a list of all the users, all their connections, and any queries we're waiting on. So its very easy for me to figure out which JDBC connection I want to kill.

That's where I get stumped though.

I've got a Connection object. It issued a really nasty query and is running on Oracle. I want to kill that connection object from another thread inside the JVM.

1. Is there a way to do this gracefully to the connection object e.g. some stealth api like:

Connection c = getConnection();
c.killImmediate()

2) If not, that's fine, I can spin up another connection and issue:

ALTER SYSTEM KILL SESSION (sid, serialNumber) IMMEDIATE

problem is, I can't figure out how to extract the server side SessionID and SerialNumber form the JDBC Connection object.

I can tell they're there b/c if I use a debugger and look at the connection object, I can see them as member variables, but I can't seem to find and API that'll bubble them up programatically.

Does anybody have any advice and/or suggestions here? I'm open to an alternate approach as well, just don't know of one myself.

Thanks,

• Pat