Reason = profile limit idle_time

Enabling limit time for idle sessions is also a good solution to manage blocking sessions with IDLE status, when the status of source blocking session is IDLE status, then activating profile limit “idle_time” is a good solution.

Step 1: Change IDLE_TIME to a fixed value (2 minutes):

SQL> alter profile default limit IDLE_TIME 2;

Step 2: Create an example of Lock:

Session N°1:

SQL> update employees set salary = salary+11 where employee_id=200;

SQL> select systimestamp from dual;
 SYSTIMESTAMP
 16-OCT-23 11.52.57.934258 AM +06:00

Session N°2:

SQL> update employees set salary = salary+110 where employee_id=200;

Step 3: After 2 Minutes in the same session N°1:

SQL> /
 select systimestamp from dual
 *
 ERROR at line 1:
 ORA-02396: exceeded maximum idle time, please connect again

Note: in the alert file:

2023-10-16T11:56:33.621238+06:00
 KILL SESSION for sid=(56, 25640):
   Reason = profile limit idle_time
   Mode = KILL SOFT -/-/NO_REPLAY
   Requestor = PMON (orapid = 2, ospid = 17870, inst = 1)
   Owner = Process: USER (orapid = 98, ospid = 21556)
   Result = ORA-0

Enjoy!