Handle block session with MAX_IDLE_BLOCKER_TIME in Oracle

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.