Alter System Kill Session Tips in Oracle

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.

Oracle Kill Session Scripts -3 and gv$session, gv$lock view

Oracle Kill Session Scripts -4 and gv$lock view

Do you want to learn Oracle Database Performance Tuning detailed, then read the following articles.

What is the Automatic SQL Tuning and How to Automated SQL Tuning in Oracle | Oracle Database Performance Tuning Tutorial -12