To illustrate and simulate locking and blocking let me first prepare a dummy table and two database accounts for simulation:

create table SH1.purchase_orders ( product varchar2(20), sales number (5));

SQL> insert into SH1.purchase_orders values (‘milk’, 20);

SQL> insert into SH1.purchase_orders values (‘corn_flakes’, 40);

SQL> insert into SH1.purchase_orders values (‘cheese’, 16);

SQL> create user jerry identified by jerry;

SQL> grant create session to jerry;

SQL> grant select,update on SH1.purchase_orders to jerry;

SQL> create user donald identified by donald;

SQL> grant create session to donald;

SQL> grant select,update on SH1.purchase_orders to donald;

// as shown below both max_idle_time, max_idle_blocker_time are set to “0” which means unlimited

SQL> show parameter idle

NAME                                 TYPE        VALUE

———————————— ———– ——————————

max_idle_blocker_time                integer     0

max_idle_time                        integer     0

In the first session as user jerry and auto-commit is to “FALSE” as shown below so you will need to explicitly end the transaction either by “commit” or “rollback”:

In the second session as user donald [ donald session is hanging because there is already shared row lock on the table]

To find blocking session:

SQL> select * from v$lock where block >0;

SQL> select SID,USERNAME,STATUS,PROGRAM from v$session where SID=290;

To  find locking:

SQL> select * from V$SESSION_EVENT where WAIT_CLASS=’Application’ and EVENT like ‘enq: TX – row lock contention’;

SID 283 is for account “donald”

So, based on the above scenario lock/block will remain unless jerry’s session commit or rollback.

Let us now explore max_idle_blocker_time parameter and set to 1 minute at the pluggable database level:

sqlplus / as sysdba

SQL> alter session set container=ORCLPDB1;

Session altered.

SQL> alter system set max_idle_blocker_time=1;

System altered.

SQL> show parameter idle

NAME                                 TYPE        VALUE

———————————— ———– ——————————

max_idle_blocker_time                integer     1

max_idle_time                        integer     0

Repeat the scenario again….

After 1 minute in jerry’s session:

The session is terminated as expected after 1 minute

And, blocking disappears however locking from “donald” session will remain so it will require either “commit” or “rollback” for the transaction being executed.