Views expressed here are solely that of my own. Please make sure that you test the code/queries that appear on my blog before applying them to the production environment.

Friday, February 19, 2010

How to kill a session which is connected to another RAC node in an Oracle 10g RAC database

If you want to kill a session which is connected to another node of an Oracle 10g RAC database, there is no one direct single command provided by Oracle which you can use, this became possible with the version 11g of Oracle RAC database.

For example, assume that you have a 2-node Oracle 10g RAC database and you are currently connected to the first node with SYS user. You want to kill a session that is connected to the second node of RAC database. This is not possible with one single command in 10g. You can use the database package that I developed below which uses database job method to kill a session that is connected on another node of Oracle 10g RAC database. I also tried to explain the situation below.

First connect to the first node of 2-node Oracle 10g RAC database.

C:\Documents and Settings\user01>sqlplus sys@server01_inst1 as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 19 09:59:03 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

Bring some information about this RAC database
SQL> set lines 200
SQL> col host_name format a30
SQL> r
1  select inst_id, instance_number, instance_name, host_name, version, status
2* from gv$instance

INST_ID INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME                      VERSION           STATUS
---------- --------------- ---------------- ------------------------------ ----------------- ------------
1               1 INST1            server01                       10.2.0.4.0        OPEN
2               2 INST2            server02                       10.2.0.4.0        OPEN

I am connected to the fist node

SQL> select instance_number, instance_name, host_name, version, status
2  from v$instance;

INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME                      VERSION           STATUS
--------------- ---------------- ------------------------------ ----------------- ------------
1 INST1            server01                       10.2.0.4.0        OPEN

Create a schema in which I will create my package
SQL> create user dbuser01 identified by dbuser01;

User created.

SQL> grant create session to dbuser01;

Grant succeeded.

SQL> grant select on gv_$session to dbuser01;

Grant succeeded.

SQL> grant alter system to dbuser01;

Grant succeeded.

SQL> grant create job to dbuser01;

Grant succeeded.

Create the package
SQL> CREATE OR REPLACE PACKAGE dbuser01.DBA_OPERATION IS
2
3  PROCEDURE Kill_Session_User(p_username VARCHAR2);
4  PROCEDURE Kill_Session_Job(p_sid     NUMBER,
5                             p_serial  NUMBER,
6                             p_inst_id NUMBER);
7  PROCEDURE Kill_Session(p_sid     NUMBER,
8                         p_serial  NUMBER);
9  END DBA_OPERATION;
10  /

Package created.

SQL>
SQL> CREATE OR REPLACE PACKAGE BODY dbuser01.DBA_OPERATION IS
2
3  PROCEDURE Kill_Session_User(p_username VARCHAR2) IS
4    d_stmt VARCHAR2(4000);
5  BEGIN
6    FOR c_stmt IN (
7      SELECT t1.sid, t1.serial#, t1.inst_id
8        FROM gv$session t1
9       WHERE t1.username = p_username
10     ) LOOP
11      Kill_Session_Job(p_sid     => c_stmt.sid,
12                       p_serial  => c_stmt.serial#,
13                       p_inst_id => c_stmt.inst_id);
14    END LOOP;
15    DBMS_OUTPUT.Put_Line('===== All the database sessions of user "'||p_username||'" killed successfully ! =====');
16
17  END Kill_Session_User;
18
19  PROCEDURE Kill_Session_Job(p_sid     NUMBER,
20                             p_serial  NUMBER,
21                             p_inst_id NUMBER) IS
22    JOB BINARY_INTEGER;
23    d_stmt VARCHAR2(4000);
24  BEGIN
25    d_stmt := 'begin dbuser01.DBA_OPERATION.Kill_Session(p_sid => '||p_sid||', p_serial => '||p_serial||'); end;';
26    SYS.DBMS_JOB.Submit(job => job, what => d_stmt, instance => p_inst_id);
27    COMMIT;
28    DBMS_OUTPUT.Put_Line(d_stmt);
29  END Kill_Session_Job;
30
31  PROCEDURE Kill_Session(p_sid     NUMBER,
32                         p_serial  NUMBER) IS
33    d_stmt VARCHAR2(4000);
34  BEGIN
35    d_stmt := 'ALTER SYSTEM KILL SESSION '''||p_sid||', '||p_serial||''' IMMEDIATE';
36    DBMS_OUTPUT.Put_Line(d_stmt);
37    EXECUTE IMMEDIATE d_stmt;
38  END Kill_Session;
39
40  END DBA_OPERATION;
41  /

Package body created.

SQL>

Create a second user which we will use in kill session test
SQL> create user dbuser02 identified by dbuser02;

User created.

SQL> grant create session to dbuser02;

Grant succeeded.

Connect to the second node of RAC with this user
C:\Documents and Settings\user01>sqlplus dbuser02/dbuser02@server02_inst2

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 19 10:22:58 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> 

Return to the first session on node1 and try to kill the session of dbuser02 which is on node2.
Query the session info of dbuser02
SQL> SELECT t1.sid, t1.serial#, t1.inst_id
2    FROM gv$session t1
3   WHERE t1.username = 'DBUSER02';

SID    SERIAL#    INST_ID
---------- ---------- ----------
260      28071          2

If we are on the same node as the session that we want to kill, we would use command below, but since the session to be killed is on another node you can not use the below command
SQL> alter system kill session '260, 28071';
alter system kill session '260, 28071'
*
ERROR at line 1:
ORA-00030: User session ID does not exist.

If the database version would have been 11g then you can could use the below command, but in 10g not
SQL> alter system kill session '260, 28071, @2';

So in that case we can use the package procedure which uses database job method and kills a specific session or all of the sessions of a specific user, no mather which node they are connected

To kill only one specific session
SQL> set serveroutput on size 1000000
SQL> exec dbuser01.DBA_OPERATION.Kill_Session_Job(p_sid => 260, p_serial => 28071, p_inst_id => 2);
begin dbuser01.DBA_OPERATION.Kill_Session(p_sid => 260, p_serial => 28071); end;

PL/SQL procedure successfully completed.

SQL> SELECT t1.sid, t1.serial#, t1.inst_id
2    FROM gv$session t1
3   WHERE t1.sid = 260
4     AND t1.serial# = 28071
5     AND t1.inst_id = 2;

no rows selected

To kill all of the sessions of a specific user no mather which node they are connected
SQL> set serveroutput on size 1000000
SQL> exec dbuser01.DBA_OPERATION.Kill_Session_User(p_username => 'DBUSER02');
begin dbuser01.DBA_OPERATION.Kill_Session(p_sid => 260, p_serial => 28071); end;
===== All the database sessions of user "DBUSER02" killed successfully ! =====

PL/SQL procedure successfully completed.

SQL> SELECT t1.sid, t1.serial#, t1.inst_id
2    FROM gv$session t1
3   WHERE t1.username = 'DBUSER02';

no rows selected

Regards,
Ural

3 comments:

Vladimir said...

This is an excellent article.
Still, if you want something more hands-on, try these:
http://vgrigorian.com/11gsimulator/1_rac11gr2.htm
http://vgrigorian.com/11gsimulator/2_rac11gr2rdbms1.htm
http://vgrigorian.com/11gsimulator/3_rac11gasm.htm
http://vgrigorian.com/11gsimulator/4_11gr2dbcreate.htm

You can find more demos (including dataguard, goldengate, streams) there at http://vgrigorian.com/

Thanks.
Vladimir Grigorian

Feroze shah said...
This comment has been removed by the author.
Ural Ural said...

Thank you for your comments.
Regards,
Ural