Forum OpenACS Q&A: Kill all Oracle queries on AOLserver client exit?
Incidentally, what is PostgreSQL's behavior in this case?
To actually stop the query from running, you can either find the
Oracle session with the runaway query and kill it with
alter system kill session 'SID,SERIAL#';",
or you can find the Oracle unix process handling the now-defunct
client (assuming you are not running Oracle MTS, of course), and kill
that process with "
Simply killing the Oracle process from the unix command line seems to work, and I suspect it is safe, but does anyone know for sure if it will always be safe? It is certainly simpler, so is there any reason to prefer the more complicated "alter system kill session ..." method?
If killing the Oracle process is always safe, then it might be a good
idea to have the
restart-aolserver script kill not only
the AOLserver process, but any Oracle processes which are children of
the AOLserver process. This would automatically kill all runaway
queries when AOLserver is restarted. Has anyone tried this?
Alternately, if you're connecting to Oracle over Net8, you can add
sqlnet.expire_time = 5
which should tell Oracle to check every 5 minutes that the client is
still connected, and kill the session of the client is gone. That
sounds useful, although I haven't tried that yet. Also, it definitely
won't wory for Bequeath Oracle connections, only Net8. Anyone used
this before? How well has it worked for you? E.g., any problems with
it wrongly killing a connection because of some transient network
In general, how have people here handled this "runaway Oracle query" problem? One of these methods above, or something different? I'm interested in you're experiences, for better or worse.
One of the background processes (smon?) looks out for abnormally terminated processes and then cleans up the mess left behind (locks, allocated blocks with half-filled transaction data, etc). Doing the "alter system" stuff lets oracle clean that stuff up directly (presumably using information associated in the oracle process that's running the session) They're probably equivalent, with the alter sytem taking less time / fewer resources. Plus you never know if there's some race condition lurking in the oracle software that'll get triggered with the oracle process going away suddently.
personally I prefer to alter system to slaughter first, and then resort to the kill program if that doesn't work / things are in dire straits.
Why not let Oracle handle thoses things ?