Kill All session from a User in Oracle RDS in AWS

SELECT s.inst_id,

       s.sid,

       s.serial#,

       p.spid,

       S.Username,

       S.Program,

       'exec rdsadmin.rdsadmin_util.kill('||s.sid||','||s.serial#||');' as Execute_command

FROM   gv$session s

       Join Gv$process P On P.Addr = S.Paddr And P.Inst_Id = S.Inst_Id

Where  S.Type != 'BACKGROUND'

  AND s.username = UPPER('&Username');

 

Example Output: 

INST_ID

SID

SERIAL#

SPID

USERNAME

PROGRAM

EXECUTE_COMMAND

1

17

6062

1074

APPUSER1

SQL Developer

exec rdsadmin.rdsadmin_util.kill(17,6062);

1

24

6428

1345

APPUSER1

SQL Developer

exec rdsadmin.rdsadmin_util.kill(24,6428);

1

659

60531

1349

APPUSER1

SQL Developer

exec rdsadmin.rdsadmin_util.kill(659,60531);

1

27

50331

1413

APPUSER1

SQL Developer

exec rdsadmin.rdsadmin_util.kill(27,50331);

1

661

9048

1434

APPUSER1

SQL Developer

exec rdsadmin.rdsadmin_util.kill(661,9048);

 

Execute the output in EXECUTE_COMMAND column as script to kill all session from the user.

Comments