Kill blocker session or deadlock automatically with Max_idle_blocker_time in Oracle

MAX_IDLE_BLOCKER_TIME specifies the maximum number of minutes that a blocking session can be idle. After a specified time the blocking session is killed automatically. The default value is 0 means unlimited value.

Check the parameter value:

SQL> show parameter idle

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_idle_blocker_time                integer     0
max_idle_time                        integer     0

max_idle_time parameter is used for all sessions either blocking or non-blocking but max_idle_blocker_Time is only for blocking sessions.

Alter the MAX_IDLE_BLOCKER_TIME to 3 minutes

alter system set max_idle_blocker_time=3;

After 3 minutes blocker session is terminated if its remains ideal with following message:

SQL> /
update test set id=21 where id=11
       *
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 84947
Session ID: 277 Serial number: 20907