How to Kill Oracle RAC Sessions

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 ‘JAMESH’:

SQL> select 'alter system kill session '||''''||sid||','||serial#||','||'@'||INST_ID||''''||' immediate;' from gv$session where USERNAME='JAMESH' 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 = 'JAMESH' group by username, machine;

no rows selected
Advertisements
This entry was posted in SQL Scripts For DBA and tagged , , , , , , , . Bookmark the permalink.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s