What is the quickest way to kill all sessions from a specified user in Oracle?

What is the quickest way to kill all sessions from a specified user in Oracle?

Solution 1:

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 kill session.

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.

Solution 2:

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!

Solution 3:

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.