What is the quickest way to kill all sessions from a specified user in Oracle?
You should be able to do it in a
PL/SQL loop. Make sure to test it with just logging the parameters before actually calling
BEGIN FOR c IN ( SELECT s.sid, s.serial# FROM v$session s WHERE s.username = 'your_user_name' ) LOOP EXECUTE IMMEDIATE 'alter system kill session ''' || c.sid || ',' || c.serial# || ''''; END LOOP; END;
See similar question on asktom.
Another approach that could run faster overall is to generate orakill commands and execute them in a DOS window:
SELECT 'orakill '||d.name||' '||spid FROM v$session s JOIN v$process p ON addr=paddr JOIN v$database d ON (1=1) WHERE s.username = 'THE_USER';
Orakill does not wait for session transactions to complete, so the kill is very fast.
Whatever your solution turns out to be, be careful!
One thing to be aware of is that killing a session in Oracle does not necessarily stop the user's session (and associated processes) from consuming resources. So a runaway query may continue to run away, consuming resources like CPU and I/O and holding locks, until it checks back in with Oracle's process monitor (pmon) process/thread.
Assuming one's running Oracle on a Unix system in "dedicated connection" mode, one can join v$session and v$process on v$session.paddr = v$process.addr to find all the sessions and processes for a given user's connections, and use the UNIX
kill commmand with
SIGKILL to kill the underlying server processes for the user's sessions. pmon will then clean up the failed session, rolling back any transactions and releasing any locks.
This is definitely a big-hammer approach when the approach Peter Lang's previously pointed out isn't killing the user's sessions aggressively enough.
Here is a very light improvement of Peter Lang's answer that I find easier to use. It is interactive and asks for the username:
PROMPT Entrer the username to kill; DEFINE username = &1 BEGIN FOR c IN ( SELECT s.sid, s.serial# FROM v$session s WHERE s.username = '&&username' ) LOOP EXECUTE IMMEDIATE 'alter system kill session ''' || c.sid || ',' || c.serial# || ''''; END LOOP; END; /