Disconnect all user connections on an RDS Oracle DB

Question

So I have an AWS RDS server instance configured with an oracle database, and I'm trying to forcibly disconnect all connections for a given user so I can drop that user and re-create the user and it's schemas.

According to the AWS RDS documentation, instead of using

ALTER SYSTEM KILL SESSION ' sid, serial#' immediate

We're supposed to use

exec rdsadmin.rdsadmin_util.kill(sid, serial#)

See Amazon's documentation here

so, I've tried both of the following ways to get kill all connections, and neither of them work:

BEGIN
  for session_to_drop in (select 'exec rdsadmin.rdsadmin_util.kill('|| sid ||',' || serial# || ')' command from v$session where username in ('SCHEMA_NAME'))
  loop
    dbms_output.put_line(session_to_drop.command);
    execute immediate session_to_drop.command;
  end loop;
end;

Error report -
ORA-00900: invalid SQL statement
ORA-06512: at line 5
00900. 00000 -  "invalid SQL statement"
*Cause:

*Action:
exec rdsadmin.rdsadmin_util.kill(22,48087)

BEGIN
  for session_to_drop in (select sid, serial# from v$session where username in ('PERFLAB')) loop
    dbms_output.put_line('exec rdsadmin.rdsadmin_util.kill(' || session_to_drop.sid || ',' || session_to_drop.serial# ||')');
    exec rdsadmin.rdsadmin_util.kill(session_to_drop.sid, session_to_drop.serial#);

  end loop;
end;

Error report -
ORA-06550: line 4, column 10:
PLS-00103: Encountered the symbol "RDSADMIN" when expecting one of the following:
   := . ( @ % ;
The symbol ":=" was substituted for "RDSADMIN" to continue.
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Ideally these loops would loop through all of the present connections and kill each one; however, it seems like the exec command isn't recognized within the loop.

Has anyone else been able to solve this problem?

Answer 1

You don't use exec inside a PL/SQL block. exec is a SQL*Plus command, it's not part of the SQL or PL/SQL language. You just call the procedure in the loop

BEGIN
  for session_to_drop in (select sid, serial# 
                            from v$session 
                           where username in ('PERFLAB')) 
  loop
    rdsadmin.rdsadmin_util.kill(session_to_drop.sid, session_to_drop.serial#);
  end loop;
end;

I would guess that you would want to lock the account before you kill the sessions in order to prevent the user from logging back in while you're in the process of killing sessions.