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
grep "ora_"also matches the background processes of the instance (for exampleora_pmon_<SID>). Runningkill -9on all matches, as in thexargscommand above, can stop the instance. Review the list before killing anything.- Use the database-level command first. The operating-system kill bypasses the normal end of the session and should be used only when the session remains after that.
- Confirm with the users before killing sessions, and target only the sessions idle for the agreed duration.
- Source: Cleanup zombies consuming CPU (Oracle blog).