Kill session in AWS Oracle Database ... 

Oracle AWS RDS kill session
connections on an RDS Oracle DB
Kill session in AWS Oracle Database

[oracle@rac01 ~]$ sqlplus 'vihaan@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=************.compute.amazonaws.com)(Port=1521))(CONNECT_DATA=(SID=orcl)))'

SQL*Plus: Release 12.2.0.1.0 Production on Sat May 5 14:58:57 2018

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

Enter password:

SQL>

*******************************************
This Will NOT work !!!!!!!!
set linesize 200 pagesize 300 
col kill for a70
select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' immediate ;' kill,username,sql_id from gv$session 
where 1=1
-- and username='username'
-- and status = 'INACTIVE'
and type != 'BACKGROUND';
      

*********************************************

select LOGINS from V$INSTANCE;
 
we need to use this pakage rdsadmin.rdsadmin_util to kill session in oracle aws rds
 
exec rdsadmin.rdsadmin_util.kill(627,35083);
 

select SID, SERIAL#, STATUS,username from V$SESSION where STATUS='INACTIVE';

 SID SERIAL# STATUS USERNAME
---------- ---------- -------- ------------------------------
 40 61773 INACTIVE RDSADMIN
  

SQL> select SID, SERIAL#, STATUS,username from V$SESSION where STATUS='INACTIVE';

 SID SERIAL# STATUS USERNAME
---------- ---------- -------- ------------------------------
 40 61773 INACTIVE RDSADMIN

SQL> exec rdsadmin.rdsadmin_util.kill(40,61773);

PL/SQL procedure successfully completed.

SQL>
   

set linesize 200 pagesize 300
sql> select sid, serial#, status,username from v$session where status='INACTIVE';

 SID SERIAL# STATUS USERNAME
---------- ---------- -------- ------------------------------
 40 61773 INACTIVE RDSADMIN

SQL> exec rdsadmin.rdsadmin_util.kill(40,61773);

PL/SQL procedure successfully completed.
 
 

begin
 rdsadmin.rdsadmin_util.kill(
 sid => &sid, 
 serial => &serial_number,
 method => 'IMMEDIATE');
end;
/

SQL> exec rdsadmin.rdsadmin_util.kill(40,61777,'IMMEDIATE');

PL/SQL procedure successfully completed.


to Kill session !
begin
 rdsadmin.rdsadmin_util.kill(
 sid => &sid, 
 serial => &serial_number,
 method => 'IMMEDIATE');
end;
/

Via Script 

set linesize 200 pagesize 300
col kill for a70
select 'exec rdsadmin.rdsadmin_util.kill('|| sid ||',' || serial# || ');' kill from v$session where username in ('&Username'); 


-- session with Immediate !!!

set linesize 200 pagesize 300
select 'exec rdsadmin.rdsadmin_util.kill('||s.sid||','||s.serial#||', ''IMMEDIATE'''||');' kill , ' -- ',username ,status,blocking_session,state,final_blocking_session,sql_id
from v$session s
where 1=1 
-- and username = upper('')
 and STATUS != 'ACTIVE'
 and s.Type != 'BACKGROUND';
 
 
KILL '--' USERNAME STATUS BLOCKING_SESSION STATE FINAL_BLOCKING_SESSION SQL_ID
---------------------------------------------------------------------- ---- -------------------- -------- ---------------- ------------------- ---------------------- -------------
exec rdsadmin.rdsadmin_util.kill(40,61783, 'IMMEDIATE'); -- RDSADMIN INACTIVE WAITING

 
SQL> SQL> exec rdsadmin.rdsadmin_util.kill(40,61783, 'IMMEDIATE');

PL/SQL procedure successfully completed.



set linesize 300 
col username for a20
select 
'exec rdsadmin.rdsadmin_util.kill('||s.sid||','||s.serial#||', ''IMMEDIATE'''||');' kill ,
 ' -- ',
 s.inst_id,
 -- s.sid,
 -- s.serial#,
 p.spid,
 s.username,
 s.program,
 s.sql_id
 from gv$session s, gv$process p
 where 1=1 
 and p.addr = s.paddr 
 and p.inst_id = s.inst_id
and s.type != 'BACKGROUND' ;

KILL '--' INST_ID SPID USERNAME PROGRAM SQL_ID
---------------------------------------------------------------------- ---- ---------- ------------------------ -------------------- ------------------------------------------------ -------------
exec rdsadmin.rdsadmin_util.kill(40,61783, 'IMMEDIATE'); -- 1 27035 RDSADMIN JDBC Thin Client
exec rdsadmin.rdsadmin_util.kill(21,62867, 'IMMEDIATE'); -- 1 23889 VIHAAN sqlplus@rac01.*****.net (TNS V1-V3) 6v5f6v3upz5mu