Monday, February 18, 2013

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




Wednesday, February 13, 2013

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