Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Jan 30 '10 at 1:25

This question came from our site for professional and enthusiast programmers.

5 Answers 5

up vote 7 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
    
Thanks for the tip :) The Oracle server we develop on runs on Windows. I've added a tag to the question. –  Thomas Bratt Feb 1 '10 at 9:35

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!

share|improve this answer

alter system checkpoint;
shutdown abort
startup restrict
shutdown immediate

:)

share|improve this answer

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;
/
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.