Kill Session Scripts in Oracle -2

Hi,

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.

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 -1

https://ittutorial.org/oracle-kill-session-scripts-3/

https://ittutorial.org/oracle-kill-session-scripts-4/

 

 

 

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

https://ittutorial.org/oracle-database-performance-tuning-tutorial-12-what-is-the-automatic-sql-tuning-and-how-to-automated-sql-tuning/