Mass killing Oracle connections

If you have started to run out of connections due to some rogue program running wild, hanging your connections, you might need to kill a session or two. This is how to achieve this

To kill a session you only need to issue
ALTER SYSTEM KILL SESSION '<SID>,<SESSION#>';
where SID and SERIAL# can be retrieved from the following command:

SELECT s.inst_id,
  s.sid,
  s.serial#, 
  p.spid,
  s.username,
  s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND'
ORDER BY username;

In my case, this often turns out to be Java clients using the JDBC layer, and to simply kill all these client connections, I do the following. Think twice before doing this in production😉

Create kill sessions statements for all clients using the JDBC stack:

SELECT 'ALTER SYSTEM KILL SESSION ''' 
  || s.sid || ',' || s.serial# || ''';' 
  as kill_line
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND'
and s.program = 'JDBC Thin Client';

Simply copy the resulting sql statements and run them. Mass killings achieved:-/

Legg igjen en kommentar

Fyll inn i feltene under, eller klikk på et ikon for å logge inn:

WordPress.com-logo

Du kommenterer med bruk av din WordPress.com konto. Logg ut / Endre )

Twitter picture

Du kommenterer med bruk av din Twitter konto. Logg ut / Endre )

Facebookbilde

Du kommenterer med bruk av din Facebook konto. Logg ut / Endre )

Google+ photo

Du kommenterer med bruk av din Google+ konto. Logg ut / Endre )

Kobler til %s

%d bloggers like this: