Home > Oracle > How to kill all of a user’s sessions

How to kill all of a user’s sessions

When dropping a user fails with sessions still connected, the following script will end all of those and allow you to complete your task.
–Kill all the Oracle connections script
DECLARE — declare variables
  CURSOR c1 IS
  select sid, serial# from v$session
  where username=’TESTUSER’;  — declare cursor
— declare record variable that represents a row fetched
   kill_it c1%ROWTYPE; — declare variable with %ROWTYPE attribute
BEGIN
— open the explicit cursor c1
  OPEN c1;
  LOOP
    FETCH c1 INTO kill_it; — retrieve record
    EXIT WHEN c1%NOTFOUND;
    BEGIN
      EXECUTE IMMEDIATE ‘alter system kill session ”’||
       kill_it.sid||’, ‘||kill_it.serial#||””;
    END;
  END LOOP;
  CLOSE c1;
END;

/

Categories: Oracle
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: