11g: RAC - Killing sessions from different instances

Hi Everyone!!

Here is one more good information for DBAs in 11g.
You can kill the sessions of a particular instance using 'alter system kill session' statement.
It has new third parameter - Instance number.

ALTER SYSTEM KILL SESSION 'SID, SERIAL#,[@INST_ID]'

Here is simple example narrates. I am in Instance 1 and killing session of instance2.

SQL> @blocker

      SID    SERIAL#    INST_ID
---------- ---------- ----------
       170     98326          2

SQL> @myses

      SID    SERIAL#    INST_ID
---------- ---------- ----------
      291     12623          1


SQL> ALTER SYSTEM KILL SESSION '170,98326,@2';

System altered.

SQL> 

You can use the below script for killing sessions from different instances in 10g

declare
job binary_integer;
inst_id number := '&inst_id';
sid number := '&sid';
serial number := '&serial';
begin
dbms_job.submit (
job=>job,
what=> 'begin execute immediate ''alter system kill session ''''' || sid || ',' || serial || '''''; end;',
instance=>inst_id
);
commit;
end;
/


Previous Related article 10g:
Killing session with immediate option in 10g
http://applicationsdba.blogspot.com/2009/04/10g-beware-killing-session-with.html