Kill All Connected Sessions for a user

Script that kills all sessions connected as AUSER

SET SERVEROUTPUT ON SIZE 1000000
BEGIN
FOR usersession IN (SELECT s.USERNAME, s.OSUSER, s.STATUS, s.SID, s.SERIAL#
FROM   v$process p,v$session s
WHERE  p.addr = s.paddr
AND    s.username = 'AUSER')
LOOP
BEGIN
IF usersession.STATUS = 'ACTIVE' THEN
EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ' || '''' || usersession.sid || ',' || usersession.serial# || '''' || 'IMMEDIATE';
ELSIF usersession.STATUS = 'INACTIVE' THEN
EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ' || '''' || usersession.sid || ',' || usersession.serial# || '''' || 'IMMEDIATE';
ElSE
DBMS_OUTPUT.put_line (usersession.sid || ' : ' || usersession.serial# || ' : ' || usersession.OSUSER ||  ' : ' || usersession.STATUS);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(usersession.sid || ' : ' || usersession.serial#  ||  ' : ' || usersession.OSUSER);
END;
END LOOP;
END;
/
exit;

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s