Kill user sessions in RAC 10g

Goal

The 10g version of Oracle RAC only allows ALTER SYSTEM KILL SESSION (SID,SERIAL#); as a local session on a local node.

To kill from one node sessions across multiple nodes the following script has been created…

Kill_Session_User(p_username VARCHAR2)

– This takes the schema/user name and will loop through all sessions for this user in gv$sessions
— Then uses supplies this SID, SERIAL#, INST_ID to Kill_Session_Job

Kill_Session_Job(p_sid NUMBER, p_serial NUMBER, p_inst_id NUMBER)
— This takes the session SID and SERIAL# along with the INSTANCE_ID number and schedules the local Kill_Session jobs on the RAC nodes

Kill_Session(p_sid NUMBER, p_serial NUMBER)
— This just does a simple ALTER SYSTEM KILL SESSION (sid,serial#)

Solution

NOTE: This can be run as SYS or SCOTT

SCOTT requires the following grants
grant alter system to SCOTT;
grant create job to SCOTT;

To Kill All Sessions For A UserName

set serveroutput on size 1000000
exec GISDBA.DBA_10g_RAC.Kill_Session_User(p_username => 'SCOTT');

Expected feedback

++ USER = “SCOTT” SID = “470” SERAL = “35488” INSTANCE = “2” +++ begin GISDBA.DBA_10g_RAC.Kill_Session(p_sid => 470, p_serial => 35488); end;
+++ USER = “SCOTT” SID = “507” SERAL = “40797” INSTANCE = “1” +++ begin GISDBA.DBA_10g_RAC.Kill_Session(p_sid => 507, p_serial => 40797); end;

===== All the database sessions of user “SCOTT” killed successfully ! ===== PL/SQL procedure successfully completed. To Kill A Session on any node

define user='SCOTT'
select username,SID, SERIAL#,INST_ID,program from gv$session where username='&user';
 
USERNAME                              SID    SERIAL#    INST_ID PROGRAM
------------------------------ ---------- ---------- ---------- ------------------------------------------------
SCOTT                                 507      40797          1 sqlplus.exe
SCOTT                                 470      35488          2 sqlplus.exe
 
set serveroutput on size 1000000
exec GISDBA.DBA_10g_RAC.Kill_Session_Job(p_sid => 470, p_serial => 35488, p_inst_id => 2);

Expected feedback

begin DBA_10g_RAC.Kill_Session(p_sid => 470, p_serial => 35488); end;

PL/SQL procedure successfully completed.