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