Here’s a small script to display, then kill all sessions in an Oracle 11gR2 database for a specific username.
First let’s check the sessions of the specific username:
NOTE: Change “<USER NAME HERE>” in both blocks of code, for the actual username to be killed.
SQL> set serveroutput on size 10000;
SELECT sid,serial#
FROM v$session
WHERE username = '<USER NAME HERE>';
Now run the code to kill the user sessions:
SQL> DECLARE
dummy NUMBER;
c NUMBER := dbms_sql.open_cursor;
BEGIN
FOR c1_row IN (SELECT sid,serial#
FROM v$session
WHERE username = '<USER NAME HERE>') LOOP
DBMS_OUTPUT.PUT_LINE('Killing session: '||c1_row.sid||' serial#: '||c1_row.serial#);
DBMS_SQL.PARSE(c,'alter system kill session '||''''||c1_row.sid||','||c1_row.serial#||'''',dbms_sql.NATIVE);
dummy := DBMS_SQL.EXECUTE(c);
DBMS_OUTPUT.PUT_LINE('Session: '||c1_row.sid||' serial#: '||c1_row.serial#||' killed.');
END LOOP;
dbms_sql.close_cursor(c);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,250));
END;
/
You can now run the check code again to see that all sessions for the user have been killed.
