LLI Wiki — My Digital Knowledge Base

Oracle: kill inactive and zombie sessions

Find and kill inactive or zombie Oracle sessions at database level and at OS level.

An Oracle instance can slow down because sessions stay idle while holding resources. The DBA can list the sessions that have been inactive for a long time and kill them, either at database level (ALTER SYSTEM KILL SESSION) or at operating-system level (kill -9 on the server process).

Identify the server processes of an instance (Unix)

ps -ef|grep "ora_"|grep -v grep|grep $ORACLE_SID|awk '{print $2}'

Find sessions idle for more than one hour

col sid for 99999
col username for a15
col last_call_et for 9999999
col et2 for 999999

select     sid,
    username,
    logon_time,
    last_call_et,
    last_call_et/60/60 et2,
    program
from     v$session
where     username is not null
and     last_call_et/60/60>1
order     by 4
/

last_call_et is the time in seconds since the session's last call; dividing by 3600 gives hours.

Generate kill commands from SQL*Plus (Unix)

The script takes the program pattern and the username as parameters (&1 and &2).

set echo off head off veri off pages 50000 lines 120
spool killzombies.sh

select 'kill -9 '||b.spid ||' # '||a.username||' '||a.program
from v$session a, v$process b
where a.program like '&1' and a.username ='&2'
and last_call_et/60/60>1
and a.paddr=b.addr
/

Then make the generated script executable and run it:

chmod 700 killzombies.sh
./killzombies.sh

Kill at database level

List the inactive sessions with their SID and serial number:

set lines 400 pages 400
col USERNAME for a30
select USERNAME, SID, SERIAL#, STATUS, SQL_ID from v$session where status='INACTIVE';

Generate the kill statements:

select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where status='INACTIVE';

Run one of them:

alter system kill session '12,19428' immediate;

The same queries with status='KILLED' list sessions that were already killed but are still present. For those, the operating-system level below is used.

Kill at operating-system level

Find the server process (SPID) of the inactive sessions:

set lines 400 pages 400
col sid format 99999
col username format a30
col osuser format a30
select a.sid, a.serial#, a.username, a.osuser, b.spid from v$session a, v$process b
  where a.paddr= b.addr
  and a.sid in (select a.sid from v$session a, v$sqlarea b, v$process c
   where a.sql_address=b.address and a.sql_hash_value=b.hash_value
   and a.paddr=c.addr
   and a.username is not null
   and a.status='INACTIVE');

Then kill the process by its SPID:

kill -9 <spid>

Only sessions idle for more than 30 minutes

When the database is very slow and users ask for it, only the sessions inactive for more than half an hour can be targeted by adding a.last_call_et>(30*60) to the query above:

  and a.last_call_et>(30*60)
  and a.status='INACTIVE');

The result is then killed with alter system kill session '<sid>,<serial#>' immediate; or with kill -9 <spid>.

Notes

Mastodon LLIMastodon SocialFosstodon