How to Kill Oracle RAC Sessions

Kill a session on any RAC instance:
SQL>alter system kill session ‘SID, SERIAL#,@INST_ID’ immediate;

We know the syntax for killing a session is :

SQL>alter system kill session 'SID, SERIAL#' immediate;

In RAC environment, the syntax will be :

SQL>alter system kill session 'SID, SERIAL#,@INST_ID' immediate;

Kill all the sessions from username ‘TESTUSER’:

SQL> select 'alter system kill session '||''''||sid||','||serial#||','
            ||'@'||INST_ID||''''||' immediate;' 
     from gv$session 
    where USERNAME='TESTUSER' order by INST_ID;

'ALTERSYSTEMKILLSESSION'||''''||SID||','||SERIAL#||','||'@'||INST_ID||''''||'IMMEDIATE;'
--------------------------------------------------------------------------
alter system kill session '3839,1429,@1' immediate;
alter system kill session '3269,62522,@1' immediate;
alter system kill session '2135,2483,@1' immediate;
alter system kill session '1994,10353,@1' immediate;
alter system kill session '1144,30683,@1' immediate;
alter system kill session '1003,59576,@1' immediate;
alter system kill session '4,33789,@1' immediate;
alter system kill session '4268,11193,@1' immediate;
alter system kill session '2846,21471,@2' immediate;
alter system kill session '2987,43213,@2' immediate;
alter system kill session '3983,62058,@2' immediate;
alter system kill session '4122,56820,@2' immediate;
alter system kill session '4125,54104,@2' immediate;
alter system kill session '4263,55362,@2' immediate;
alter system kill session '4268,43052,@2' immediate;
alter system kill session '2704,41337,@2' immediate;
alter system kill session '2564,24435,@3' immediate;
alter system kill session '1851,17069,@3' immediate;
alter system kill session '857,25207,@3' immediate;
alter system kill session '4408,37890,@3' immediate;
alter system kill session '576,38233,@4' immediate;
alter system kill session '432,5600,@4' immediate;
alter system kill session '6,29398,@4' immediate;
alter system kill session '719,42236,@4' immediate;

24 rows selected.

Check sessions are killed :

SQL> select username, machine,count(*) 
       from gv$session 
      where USERNAME = 'TESTUSER' 
   group by username, machine;

no rows selected
Advertisement

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 )

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: