Kill multiple sessions at a time in Oracle

Here the story begins, I tried dropping a user and end up with the below error:

ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

I checked the associated sessions,

select sid, serial# from v$session where username ='DBUSER';

If this returns a single row, you can simply kill the session using the below command

alter system kill session 'sid,serial#';

what if I have multiple rows?The below procedure will help you to do this:

create or replace
PROCEDURE "KILL_ALL_SESSION" AS
BEGIN
declare
str varchar2(100);
lockUser varchar2(100);
unLockUser varchar2(100);
BEGIN
lockUser := 'alter user DBUSER account lock';
unLockUser:= 'alter user DBUSER account unlock';
execute immediate lockUser;
dbms_output.put_line(lockUser);
begin
for S in (select sid,serial# from v$session where username ='DBUSER'
and username != 'SYS')
loop
str := 'alter system kill session '||chr(39)||S.sid||','||S.serial#||chr(39);
dbms_output.put_line(S.sid);
execute immediate str;
end loop;
end;
END ;
END KILL_ALL_SESSION;

Okay, what is the use of lockUser/unLockUser here? Well, this is needed, to drop users who automatically establish session. So that time we may need to lock the account for further connection attempts.

We can lock/unlock the users using the following commands:

alter user DBUSER account lock;
alter user DBUSER account unlock;