Auto-terminating idle sessions

How can I get Oracle to automatically terminate sessions based on some idle time parameter (for example, elapsed time since last call > 10 minutes, or something like that)?

I would have expected there to be a configuration parameter I could set that determines the max lifetime of a session.. but there doesn't seem to be anything like that.

My next thought was to have Oracle run a stored proc periodically to check for idle sessions, and kill 'em.. something like

select sid,serial#,status,last_call_et from v$session where last_call_et > 600 and status='inactive'

and calling 'alter system kill session' over the results.

Of course, PL/SQL doesn't allow you to use 'alter system' (I can't see why not..), so that's out of the question.

Now I'm going to try and write the same stored proc in Java, where presumably I can execute whatever the hell I like.

But this all seems so complicated for what I would expect to be a fairly common requirement.

In case it helps, here's some context: I'm writing a JDBC app that opens connections to an oracle server.. the problem is that it's not possible to guarantee that the app will properly shut down (someone might reboot the machine for example), so I can't guarantee that the oracle sessions will be closed by the app.

thanks! Received on Wed Jun 21 2000 - 00:00:00 CDT