阻塞会话：
当一个会话持有其它会话所需要的资源时，此会话会被认为是阻塞会话，比如以下场景：
a、 阻塞会话持有另一个会话所需要的锁.
b、 阻塞会话为一个并行操作，并且它的使用者组、PDB、或数据库已达到其最大并行服务器限制或已排队的并行操作.
c、 阻塞会话所在的PDB或数据库实例达到其SESSIONS或PROCESSES限制.
另外，MAX_IDLE_BLOCKER_TIME不同于MAX_IDLE_TIME，后者适用于所有会话，此前博客有MAX_IDLE_TIME参数的相关介绍，此处不再赘述，前者仅适用于阻塞会话.因此为使MAX_IDLE_BLOCKING_TIME有效，其限制必须小于MAX_IDLE_TIME限制.
注意：
a、 该参数对并行查询进程以及sys用户的会话没有影响.
b、 该参数从oracle 19c开始引进.
c、 该参数缺点：阻塞会话自动kill后，DBA不清楚阻塞会话执行过的SQL信息，不方便追溯.
2、测试
2.1、修改max_idle_blocker_time
[oracle@dbserver ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 16 09:26:02 2022
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SQL> conn sys/oracle_4U@orclpdb as sysdba
Connected.
SQL> col issys_modifiable for a20
SQL> col name for a25
SQL> col value for a15
SQL> r
1* select name,value,issys_modifiable from v$parameter where name='max_idle_blocker_time'
NAME VALUE ISSYS_MODIFIABLE
------------------------- --------------- --------------------
max_idle_blocker_time 0 IMMEDIATE
SQL> ALTER SYSTEM SET max_idle_blocker_time=2 SCOPE=BOTH PDB='ORCLPDB';
System altered.
SQL> select name,value,issys_modifiable from v$parameter where name='max_idle_blocker_time';
NAME VALUE ISSYS_MODIFIABLE
------------------------- --------------- --------------------
max_idle_blocker_time 2 IMMEDIATE
2.2、测试sys用户
会话1操作：
SQL> conn sys/oracle_4U@orclpdb as sysdba
Connected.
SQL> select sid from v$mystat where rownum=1;
SID
----------
402
SQL> select sid,serial# from v$session where sid=402;
SID SERIAL#
---------- ----------
402 29176
SQL> create table test (id number(5),
2 name varchar2(20));
Table created.
SQL> insert into test values (1,'jack');
1 row created.
SQL> insert into test values (2,'marry');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test where id=1 for update;
ID NAME
---------- -------------------------
1 jack
会话2操作：
[oracle@dbserver ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 16 10:13:37 2022
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SQL> conn sys/oracle_4U@orclpdb as sysdba
Connected.
SQL> select sid from v$mystat where rownum=1;
SID
----------
147
SQL> select sid,serial# from v$session where sid=147;
SID SERIAL#
---------- ----------
147 35160
SQL> host date
Wed Nov 16 10:14:57 CST 2022
SQL> update test set name='mike' where id=1;
SQL> host date
Wed Nov 16 10:18:25 CST 2022
说明：会话2会一直卡在update，会话1不会自动断开连接.
2.3、测试普通用户
会话1操作：
SQL> conn ora1/ora1@orclpdb
Connected.
SQL> select * from test for update;
ID NAME SALARY
---------- ------------------------- ----------
1 cherry 20000
2 jack 25000
3 miss 30000
4 23000
会话2操作：
SQL> conn ora1/ora1@orclpdb;
Connected.
SQL> update test set salary=25000 where id=1;
1 row updated.
说明：会话2在发出更新test表的语句后，不会立即更新，而是需要等待2分钟.
以下是trace日志信息：
2022-11-16T10:28:50.279559+08:00
ORCLPDB(3):Process termination requested for pid 5637 [source = rdbms], [info = 2] [request issued by pid: 4188, uid: 1001]
2022-11-16T10:28:50.331587+08:00
(3):KILL SESSION for sid=(402, 11472):
(3): Reason = max_idle_blocker_time parameter
(3): Mode = KILL HARD SAFE -/-/NO_REPLAY
(3): Requestor = PMON (orapid = 2, ospid = 4188, inst = 1)
(3): Owner = Process: USER (orapid = 63, ospid = 5637)
(3): Result = ORA-0
会话1执行以下语句，正如预想的结果，已自动断开连接.
SQL> select sid from v$mystat where rownum=1;
select sid from v$mystat where rownum=1
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 5637
Session ID: 402 Serial number: 11472
