How to use the parameters max_idle_blocker_time in Oracle 21c

 

 In Oracle 21c we have two new parameters.

• MAX_IDLE_TIME
• MAX_IDLE_BLOCKER_TIME

For the parameter MAX_IDLE_BLOCKER_TIME, oracle documentation provides the following definition:

MAX_IDLE_BLOCKER_TIME: Sets limits only for idle sessions consuming resources. It specifies the maximum number of minutes that a blocking session can be idle. After that point, the session is automatically terminated.

More info: https://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/MAX_IDLE_BLOCKER_TIME.html

 

Let's test how it works:

I will Logging into a PDB with the user "SYS".

SQL> show con_name

CON_NAME
------------------------------
PDB11

Setting the parameter: 

SQL> alter system set  set max_idle_blocker_time=1 scope=both;

System altered.

Session 1: I will create a session with the user "DGOMEZ" and I will perform one update against the table dgomez.table1 so that this sessions is "consuming resources" and is blocking the other session.

22:42:05 SQL> update dgomez.table1 set col1=2 where col1=1;

1 row updated.

 Session 2: In another session, I will create another session with the user "DGOMEZ" and I will execute the same update, so that this session will be blocked by the session 1.

 SQL> update dgomez.table1 set col1=2 where col1=1;

The session 2 was waiting for 1 minutes and the I got the output:

1 row updated.

SQL>

Session 1: I saw that status of the session 1 by executing a SELECT statement and I confirmed it was killed:

22:42:39 SQL> select 1 from dual;
select 1 from dual
              *
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 18351
Session ID: 100 Serial number: 55963

 

As expected, the session 1 was killed after 1 minutes of being using resources and being blocking another session.