How to evict or "kill" open connections when these are above a fixed limit (using dbcp w/ oracle)

Question

Hi all, Does anyone know how to evict or kill open connections (in use or not it doesn't matter) if the number of connections is above of a fixed limit (e.g. maxActive) Currently I'm using DBCP from Apache under a Sun One 6.1. Thanks in advance!,

Answer 1

ALTER SYSTEM KILL SESSION 'nnn,mmmm' can kill sessions (with nnn being the SID and mmmm the SERIAL#). You can look at v$session seconds_in_wait and with an event of 'SQL*Net message from client' for sessions that haven't been doing anything in a while. That event basically says "I'm waiting for a client to tell me what to do next."

Also make sure the session doesn't have an open transaction

select sid, serial# from v$session 
where event = 'SQL*Net message from client'
and saddr not in (select ses_addr from v$transaction)
order by seconds_in_wait desc;

It's an ugly solution though.