Tuesday, June 16, 2009

Oracle11g - Killing session in RAC (in remote instance)

Prior to Oracle11g, whenever you want to kill a session connected to a non-local (remote) instance, you had to make an explicit connection to that particular instance and then attempt to kill.

Example:

SQL> Select instance_number from v$instance;

INSTANCE_NUMBER
---------------
1 ---> I am connected to instance 1 (Local Instance)

1 row selected.

SQL> Select inst_id, sid, serial# from gv$session where username='CHANDRA';

INST_ID SID SERIAL#
---------- ---------- ----------
2 125 7526 ---> CHANDRA is connected to Inst# 2 (Non-local/remote Instance)

1 row selected.

With 11g, you could kill the session which is connected to instance 2, while you are connected to instance 1:

SQL> Alter system kill session '125,7526,@2' immediate; #--@2 indicates remote instance.

System altered.

SQL> Select inst_id, sid, serial# from gv$session where username='CHANDRA';

no rows selected

This really makes life easy - especially you are using SQL*Plus and not any other front-end tool.