Oracle: Resolve blocking processes

By | January 12, 2016

Sometimes an oracle query goes nuts and blocks the DB.
To find the blocked processes started by user MYUSER execute:


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.username like 'MYUSER';

To kill the blocking process:


ALTER SYSTEM KILL SESSION 'sid,serial#'

Advertisements