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?

From serverfault Thomas Bratt

• 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.

  From Peter Lang

• 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.

  Thomas Bratt : Thanks for the tip :) The Oracle server we develop on runs on Windows. I've added a tag to the question.
  From Adam Musch

• 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!

  From DCookie

• alter system checkpoint;
  shutdown abort
  startup restrict
  shutdown immediate
  
  :)

  From Marcel

• 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;
  /
  

  From Farzy