How to Find and Kill Blocking sessions in RDS Oracle

February 8, 2023

You cannot use the “alter system kill session” command to terminate blocking sessions in RDS Oracle instead of that you need to use rdsadmin.rdsadmin_util.kill procedure to kill RDS user sessions. Blocking locks will be released once the process is terminated.

Due to restrictions you are not allowed to execute “alter system kill command” directly in RDS Oracle. You may get below error after it’s execution. 

SQL> alter system kill session '627,35083';
alter system kill session '627,35083'
*
ERROR at line 1:
ORA-01031: insufficient privileges

In previous post, we discussed about troubleshooting blocking sessions in Oracle Database, you can check below article which contains blocking queries may will help you to fetch required information regarding locks.

Related Post: Troubleshoot Oracle Blocking Locks
Find Table Locks in Oracle

To Kill user session, you need sid and serial# of the database user. You can find those details using below queries:

Query 1:
SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE USERNAME = 'RDS_USER';
Note: Replace USERNAME with your database user.

Query 2:
select l1.sid, ' IS BLOCKING ', l2.sid,l1.type,l2.type,l1.lmode,l2.lmode
 from gv$lock l1, gv$lock l2
 where l1.block =1 and l2.request > 0and l1.id1=l2.id1
 and l1.id2=l2.id2;

Query 3:
select 
    (select username from v$session where sid=a.sid) blocker,
    a.sid,
    ' is blocking ',
    (select username from v$session where sid=b.sid) blockee,
    b.sid
 from 
    v$lock a, 
    v$lock b
 where 
    a.block = 1
 and 
    b.request > 0
 and 
    a.id1 = b.id1
 and 
    a.id2 = b.id2;

Command to Kill Blocking sessions in RDS:

BEGIN
    rdsadmin.rdsadmin_util.kill(
        sid    => sid, 
        serial => serial_number,
        method => 'IMMEDIATE');
END;
/

Killing Process at OS Level:

BEGIN
    rdsadmin.rdsadmin_util.kill(
        sid    => sid, 
        serial => serial_number,
        method => 'PROCESS');
END;
/

Cancel SQL statements in a session:

select SID, SERIAL#, SQL_ID, STATUS from V$SESSION where USERNAME = ‘RDSUSER';

begin
    rdsadmin.rdsadmin_util.cancel(
        sid    => sid, 
        serial => serial_number,
        sql_id => sql_id);
end;
/

To Kill Multiple Sessions in RDS Oracle:

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' ;

Must Read: