HowTo: Script to kill all oracle 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#';

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

---

How to connect SQLPlus without adding SID in 'tnsnames.ora'

The following command is used to connect the remote DB without adding the service name(SID) in 'tnsnames.ora'
Example: Input the below cmd in the terminal to connect the remote db.
sqlplus user@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <Remote-host> )(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = <SID>)))

---

Vasanth KG