Loading....

One of the new features in Oracle 21c is the MAX_IDLE_BLOCKER_TIME parameter.

So if a “blocking” session is pretty much idle, all the other possibly many “blocked” sessions do not have to keep waiting until the blocker session issues a commit or rollback.

In Oracle Database 19c we could specify an amount of time that a session can be idle after which it is terminated via the resource plan directive MAX_IDLE_TIME.

But there was no option related to limiting idle time for a session which was blocking all other sessions.

This is now possible in Oracle 21c!

#####################################################################################################
Set parameter MAX_IDLE_BLOCKER_TIME value to 2 minutes
#####################################################################################################


SQL> show parameter idle

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

SQL> alter system set max_idle_blocker_time=2;

System altered.


#####################################################################################################
Session ONE perform an Update statement but do not commit or rollback
#####################################################################################################

02:00:44 SQL>  conn demo/DreamLiner787##@pdb1
Connected.

02:01:17 SQL> select * from tab1;

        ID
----------
         1
         2
         3
         4
         5

02:01:27 SQL> update tab1 set id=2 where id=1;

1 row updated.


#####################################################################################################
Session TWO performing Update on same row now hangs
#####################################################################################################

[oracle@db21c ~]$ sqlplus  demo/DreamLiner787##@pdb1

SQL*Plus: Release 21.0.0.0.0 - Production on Thu Jan 21 02:01:52 2021
Version 21.1.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Last Successful login time: Thu Jan 21 2021 02:01:17 +00:00

Connected to:
Oracle Database 21c EE High Perf Release 21.0.0.0.0 - Production
Version 21.1.0.0.0

SQL> set time on
02:01:57 SQL>  update tab1 set id=22 where id=1;


#####################################################################################################
After about 2 minutes session ONE is now terminated automatically because it was idle
#####################################################################################################

02:04:52 SQL> /
update tab1 set id=2 where id=1
       *
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 84947
Session ID: 277 Serial number: 20907



#####################################################################################################
Session TWO transaction is completed as there is no row level lock lock now
#####################################################################################################


02:01:57 SQL>  update tab1 set id=22 where id=1;

1 row updated.

02:04:25 SQL> commit;

Commit complete.

02:04:43 SQL>


Please follow and like us:

Last Update: January 21, 2021  

January 21, 2021 746 Gavin Soorma
Total 1 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

, , , , , , ,

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?