Kill all blocking objects in Oracle database

Subash Chandran 6th January 2022 Leave a Comment

To list down all blocking objects with status as ‘is blocking’, we can run the following query.

SELECT
	(
	SELECT
		username
	FROM
		v$session
	WHERE
		sid = a.sid) blocker,
	a.sid,
	' is blocking ',
	(
	SELECT
		username
	FROM
		v$session
	WHERE
		sid = b.sid) blockee,
	b.sid
FROM
	v$lock a,
	v$lock b
WHERE
	a.block = 1
	AND
   b.request > 0
	AND
   a.id1 = b.id1
	AND
   a.id2 = b.id2;

There is a handy query that would frame ‘KILL’ queries for the blocking processes.

SELECT
	'alter system kill session ''' || SID || ',' || s.serial# || ',@' || inst_id || ''';',
	sid,
	username,
	serial#,
	process,
	NVL (sql_id,
	0),
	sql_address,
	blocking_session,
	wait_class,
	event,
	p1,
	p2,
	p3,
	seconds_in_wait
FROM
	gv$session s
WHERE
	blocking_session_status = 'VALID'
	OR sid IN (
	SELECT
		blocking_session
	FROM
		gv$session
	WHERE
		blocking_session_status = 'VALID');

The output of the above query would be a list of alter statements and when you run them your blocking objects would be killed.

alter system kill session '9,9982,@1';
alter system kill session '19,20382,@1';
alter system kill session '22,18585,@1';
alter system kill session '30,60511,@1';
alter system kill session '31,48058,@1';
alter system kill session '32,33995,@1';
alter system kill session '33,24906,@1';
alter system kill session '150,57046,@1';
alter system kill session '160,26621,@1';
alter system kill session '161,22952,@1';
alter system kill session '162,43351,@1';
alter system kill session '163,46371,@1';
alter system kill session '164,11425,@1';
alter system kill session '166,14016,@1';
alter system kill session '284,19977,@1';
alter system kill session '286,45769,@1';
alter system kill session '287,27807,@1';
alter system kill session '410,1537,@1';
alter system kill session '414,6004,@1';
alter system kill session '415,6808,@1';
alter system kill session '417,54866,@1';
alter system kill session '419,37961,@1';