Oracle Kill all sessions for a user
I was recently with a client deploying some software and while talking with him he received a non related call asking him to kill all of the Oracle Sessions for a user (some bad application had used up all of the available connections). Anyway I thought to myself I know how to Kill a session in Oracle and I know how to write a loop with a cursor so I whipped him up a little script to use in the future.
Kill all sessions in Oracle script
--Kill all the Oracle connections script
DECLARE -- declare variables
CURSOR c1 IS
select sid, serial# from v$session
where username='ADPE'; -- 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;
May this save you time in the future