Under RAC, alter system kill session reports an error ORA-00030: User session ID does not exist
In the RAC environment, the KILL SESSION Times is wrong about ORA-00030 User session ID does not exist.
Problem reappearance:
NODE2:
[oracle@node2 ~] $sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Feb 11 21:27:08 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP
Data Mining and Real Application Testing options
SQL > conn sh/sh
Connected.
SQL > show parameter instance_number
NAME TYPE VALUE
-
Instance_number integer 2
SQL > select sid, serial# from v$session where sid=userenv ('sid')
SID SERIAL#
--
136 5
SQL > create table test as select a. * from all_objects a month allpapers objects b repositories objects c
NODE1:
[oracle@node1 ~] $sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Feb 11 21:25:53 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP
Data Mining and Real Application Testing options
SQL > show parameter instance_number
NAME TYPE VALUE
-
Instance_number integer 1
SQL > alter system kill session '136 5'
Alter system kill session '136. 5'
*
ERROR at line 1:
ORA-00030: User session ID does not exist.
Node2:
SQL > alter system kill session '136, 5, 5, 2'.
System altered.
Node1:
SQL > create table test as select a. * from all_objects a month allpapers objects b repositories objects c
Create table test as select a. * from all_objects a month allpapers objects bpr allcards objects c
*
ERROR at line 1:
ORA-00028: your session has been killed
ORA-00028: your session has been killed
Summary: for cross-instance kill sessions in RAC environment, alter system kill session needs to add @ instance_number. Instance_number can be obtained through show parameter instance_number.
