How can I clean up dead connections using Oracle?

Question

Right now I have a few new applications being developed against an Oracle Database, and sometimes they crash or fail to end correctly, etc... anyways the problem is they sometimes seem to leave their connections open, and I need to cleanup after them. My question is if there is a way from the database-side of things to determine dead connections and clean them up?

Answer 1

Here's a page referring to connection timeout parameters you can set in Oracle 11g. I think the 'Abandon Connection Timeout' is what you're looking for.

Answer 2

You may also be interested in killing them. Running this script in SQL*Plus will give you a list of "kill" statements. You can pick out the ones you want to kill based on the sid and run those. Oracle has some of it's own internal connections, do not kill them.

SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';     ' || sql_id death
FROM v$session
/

Answer 3

I believe you are looking for the SQLNet.ora parameter EXPIRE_TIME which tells the database to send a probe to the client every few minutes to verify that the connection is still alive.