I will explain Alter System Kill Session Tips in Oracle in this post.
Sometimes application developers or client offers you to kill any session or sessions group like SQL Net Client, or JDBC Client sessions or RMAN sessions.
Alter System Kill Session
You can kill any session with the following command.
alter system kill session 'SID, SERIAL, @INST_ID';
For example; You can kill any session which SID 63, SERIAL# 1453 and INST_ID is 2 as follows. Following command is valid for Oracle RAC.
alter system kill session '63, 1453, @2';
If your database is not RAC and it is single node, you can kill this session as follows.
alter system kill session ’63, 1453′;
You need to find session SID and SERIAL# with below script.
select s.SID,s.SERIAL#,S.USERNAME from v$session s where s.sid=63;
Oracle Kill Session
You can kill any session with its SID and SERIAL# number like below.
alter system kill session '63,1963';
Customer offers you to kill sessions group like SQL Net Client, or JDBC Client sessions or RMAN sessions. You can generate kill session script like below. You can change event to kill any other event group sessions.
SELECT 'kill -9 ' || p.spid, s.username, 'alter system kill session ''' || SID || ',' || s.serial# || ''';' FROM v$session s, v$process p WHERE s.paddr = p.addr(+) AND s.SID IN (SELECT SID FROM v$session_wait WHERE event LIKE 'SQL*Net message from client%') and s.username ='DEVECI' and s.saddr not in ( select SES_ADDR from v$transaction );
Sometimes you have just SQL_ID and you need to find sessions related with this SQL_ID, then you can find like below and you can generate kill script like below.
SELECT 'kill -9 ' || p.spid, s.username, 'alter system kill session ''' || SID || ',' || s.serial# ||',@'||p.inst_id|| ''';' FROM gv$session s, gv$process p where s.SQL_ID like '4p5w3j8b3yhcw' and s.PADDR = p.ADDR (+) and s.STATUS='ACTIVE' order by 1;
You can execute result of above query to kill sessions.
You can kill RMAN sessions which gives extra efor to the database like below.
SELECT 'kill -9 ' || p.spid, s.username,
'alter system kill session ''' || SID || ',' || s.serial# ||',@'||p.inst_id|| ''';'
FROM gv$session s, gv$process p
WHERE s.paddr = p.addr(+)
and s.TYPE ='USER'
and s.program like 'rman%';
You can read other Kill Session Scripts in Oracle.