How to Kill a Session on RAC Database In Instance Independent Fashion. (Doc ID 869777.1)

How to Kill a Session on RAC Database In Instance Independent Fashion. (Doc ID 869777.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.1.0.6 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.

GOAL

You can now kill a session in RAC database irrespective of the instance on which the session is spawned.  现在，无论在哪个实例上，都可以终止RAC数据库中的会话
This is a new feature introduced in 11g   这是11g中引入的新功能

SOLUTION

To kill a session in RAC database use the following syntax.  要终止RAC数据库中的会话，请使用以下语法

sql> conn / as sysdba

sql> alter system kill session 'sid, serial#, @inst_id';

NOTE: In the images and/or the document content below, the user information 
and data used represents fictitious data from the Oracle sample schema(s) or 
Public Documentation delivered with an Oracle database product. Any similarity to 
actual persons, living or dead, is purely coincidental and not intended in any manner.

For Example :

SQL@racdb1> select inst_id,sid,serial#,username from gv$session where username is not null and username not like 'SYS%';

INST_ID SID SERIAL# USERNAME
---------- ---------- ---------- ------------------------------
1                123        76       DBSNMP 

1 rows selected.

SQL@racdb2> conn scott/tiger
Connected.

SQL@racdb1> select inst_id,sid,serial#,username from gv$session where username is not null and username not like 'SYS%';

INST_ID SID SERIAL# USERNAME
---------- ---------- ---------- ------------------------------
2                115    17816        SCOTT
1                123         76         DBSNMP 

2 rows selected.

SQL@racdb1> alter system kill session '115,17816,@2';

System altered.

SQL@racdb1> conn scott/tiger
Connected.

SQL@racdb1> select inst_id,sid,serial#,username from gv$session where username is not null and username not like 'SYS%';

INST_ID SID SERIAL# USERNAME
---------- ---------- ---------- ------------------------------
1                110      45316         SCOTT
1                123            76         DBSNMP

2 rows selected.

sql> alter system kill session '110,45316,@1';

System altered.