Using Google Chrome?

Download my
free Chrome Extension, Power Notes Searcher, to make searching for and evaluating SAP notes, much easier.

Recent Posts

Thursday, October 03, 2013

Kill All Oracle Sessions For Specific User - 11gR2

Print Friendly and PDF
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.

No comments: