Script to kill all sessions of a user in Oracle.
BEGIN FOR r IN (select sid,serial# from v$session where username = 'USER') LOOP EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid || ',' || r.serial# || ''''; END LOOP; END;
Retrieve session identifiers and session serial number (which uniquely identifies a session’s objects):
select sid, serial# from v$session where username = 'USER'
The syntax to kill session is
alter system kill session 'sid,serial#'
alter system disconnect session 'sid,serial#' post_transaction; alter system disconnect session 'sid,serial#' immediate;
How to schedule the script from the linux or unix level? Thanks.
You can schedule the script on the database level:
Made a small modification to make this work on a RAC environment
All you need is to change the inst_id and username like if you are specific about the instance and the username to be killed
FOR r IN (select username,inst_id, sid,serial# from gv$session where username like ‘%’ and status=’INACTIVE’ and inst_id=’1′)
EXECUTE IMMEDIATE ‘alter system kill session ”’ || r.sid
|| ‘,’ || r.serial# || ””;
This may be some how nooby But i can not understand what is all about these ‘ you inserted beside each other ???
there are 4 single quotes at the end … what does it mean and what’s the usage.
i should add the script works as a charm and this is for my own interest …