Skip to content

How Oracle Kill Blocking Sessions

  • by

After the blocking sessions are identified, DBA should notify the session owners and leave them a chance to exit sessions gracefully by themselves. If things go worse, DBA must terminate these sessions right away.

Killing Blocking Sessions

For Separate Nodes

Here is a sample SQL which can both identify and compose kill session immediate statements of blocking sessions.

[oracle@test ~]$ vi ComposeKillBlockerStatement.sql
column kill_sql format a50;
SELECT DISTINCT blocker.inst_id,
  'alter system kill session '''
  ||blocker.sid
  ||','
  ||sess.serial#
  ||''' immediate;' kill_sql
FROM
  (SELECT * FROM gv$lock WHERE block != 0 AND type = 'TX'
  ) blocker ,
  GV$SESSION SESS
WHERE blocker.type ='TX'
AND blocker.sid    = sess.sid
AND blocker.inst_id=sess.inst_id
ORDER BY 1;

Let's see the execution result.

SQL> @ComposeKillBlockerStatement.sql

   INST_ID KILL_SQL
---------- --------------------------------------------------
         1 alter system kill session '125,225' immediate;
         2 alter system kill session '94,31' immediate;

Next, execute the results in each instance to kill the sessions. But, wait a minute, it seems a little inconvenient for DBA to issue the kill session statements separately in a cluster database.

For All Nodes

Therefore, we should enhance the above SQL.

[oracle@test ~]$ vi ComposeKillBlockerStatement_enhanced.sql
column kill_sql format a50;
SELECT DISTINCT 'alter system kill session '''
  ||blocker.sid
  ||','
  ||SESS.SERIAL#
  ||',@'
  ||blocker.inst_id
  ||''' immediate;' kill_sql
FROM
  (SELECT * FROM gv$lock WHERE block != 0 AND type = 'TX'
  ) blocker ,
  GV$SESSION SESS
WHERE blocker.type ='TX'
AND blocker.sid    = sess.sid
AND blocker.inst_id=sess.inst_id
ORDER BY 1;

We add INST_ID in the executable statements to allow DBA to issue the statements in one connection. It will be more suitable for a cluster database. Let's see the composed statements.

SQL> @ComposeKillBlockerStatement_enhanced.sql

KILL_SQL
--------------------------------------------------
alter system kill session '125,225,@1' immediate;
alter system kill session '94,31,@2' immediate;

Let's kill the sessions now.

SQL> alter system kill session '94,31,@2' immediate;

System altered.

SQL> alter system kill session '125,225,@1' immediate;

System altered.

SQL> @ComposeKillBlockerStatement_enhanced.sql

no rows selected

Of course, you can spool the result to a SQL script for executing immediately.

[oracle@test ~]$ vi ComposeKillBlockerStatement_enhanced_Batch_Kill.sql
set heading off;
column kill_sql format a50;
spool batch_kill.sql;
SELECT DISTINCT 'alter system kill session '''
  ||blocker.sid
  ||','
  ||SESS.SERIAL#
  ||',@'
  ||blocker.inst_id
  ||''' immediate;' kill_sql
FROM
  (SELECT * FROM gv$lock WHERE block != 0 AND type = 'TX'
  ) blocker ,
  GV$SESSION SESS
WHERE blocker.type ='TX'
AND blocker.sid    = sess.sid
AND blocker.inst_id=sess.inst_id
ORDER BY 1;
spool off;
@batch_kill.sql

Leave a Reply

Your email address will not be published.