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