Script to kill all sessions by a user

 Normally to Kill a session connected by a user in oracle, you should collect the sid and serial# from the view V$SESSION and execute the following command with sid and serial# filled in it.

ALTER SYSTEM KILL SESSION 'sid,serial#';

Read more about killing a session Here



We can create a PL/SQL script to kill all the sessions based ton the same, as given below


 begin    
  for x in (select Sid, Serial# from v$session where Username=upper('TypeUserNameHere'))
  loop 
   execute immediate 'Alter System Kill Session '''|| x.Sid || ',' || x.Serial# || ''' IMMEDIATE'; 
  end loop; 
end;
/

If you want to embed it in to a bash (shell) script, it will be like the following script

 
#!/bin/bash
sqlplus -silent /nolog  << EOS
connect / as sysdba
begin    
  for x in (select Sid, Serial# from v$session where Username=upper('TypeUserNameHere'))
  loop 
   execute immediate 'Alter System Kill Session '''|| x.Sid || ',' || x.Serial# || ''' IMMEDIATE'; 
  end loop; 
end;
/
EOS
exit