How to use the parameters MAX_IDLE_TIME in Oracle 21c

 In Oracle 21c we have two new parameters.

• MAX_IDLE_TIME
• MAX_IDLE_BLOCKER_TIME

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

MAX_IDLE_TIME sets limits for all idle sessions. it specifies the maximum number of minutes that a session can be idle. After that point, the session is automatically terminated.

More info: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/MAX_IDLE_TIME.html

This parameter will help a lot those customers that like to create Jobs to kill sessions that have been connected for a long time. 

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 max_idle_time=1 scope=both;

System altered.

I will create a session with the user "DGOMEZ"

SQL> set time on
22:32:54 SQL> select 1 from dual;

         1
----------
         1

After waiting for a minute, I tried to execute the same query:

22:33:00 SQL>  select 1 from dual;
 select 1 from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 18131
Session ID: 78 Serial number: 15702

 

The session was killed as expected.