idle_time results in performing a soft kill of the session.
What really happens behind the scene?
a. The session is terminated in the db and is still visible in v$session and v$process, which means they are still accounted towards the resource limit like sessions and process
b. So when are these sessions going to be released, they will be released when client acknowledges that the session is terminated
Let us see in action:
1. Connect using dbeaver client or another sqlplus client
a. we know dbeaver with a sql editor launches 3 sessions
set lines 1200 pages 3000 colsep , time on timing on trim on trims on
alter session set nls_Date_format='DD/MON/YYYY HH24:MI:SS';
select b.sid,b.serial#,substr(b.program,1,12) pgm,a.spid,b.process,b.status,b.sql_id,b.prev_sql_id,b.logon_time,b.sql_exec_start,b.prev_exec_start from v$session b,v$process a where a.addr=b.paddr and b.username='INVENTORYADM' order by b.program,b.sid;
Output:
SID, SERIAL#,PGM ,SPID ,PROCESS ,STATUS ,SQL_ID ,PREV_SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PREV_EXEC_START
----------,----------,------------------------------------------------,------------------------,------------------------,--------,-------------,-------------,--------------------,--------------------,--------------------
125, 24667,DBeaver 23?0 ,6768 ,1234 ,INACTIVE, ,b3s1x9zqrvzvc,21/APR/2024 17:00:57, ,21/APR/2024 17:00:57
138, 15750,DBeaver 23?0 ,6770 ,1234 ,INACTIVE, ,73b7u0bsntwxp,21/APR/2024 17:00:57, ,21/APR/2024 17:01:13
69, 11844,DBeaver 23?0 ,6772 ,1234 ,INACTIVE,5rsm4y10jd4p2,5rsm4y10jd4p2,21/APR/2024 17:00:57, ,21/APR/2024 17:01:14
133, 30476,sqlplus@vcen ,6798 ,6793 ,INACTIVE, ,g4y6nw3tts7cc,21/APR/2024 17:01:09, ,21/APR/2024 17:01:08
Elapsed: 00:00:00.00
2. Leave the session idle until the idle_Time parameter
3. Check what happened to the session - we know it will be in killed state
set lines 1200 pages 3000 colsep , time on timing on trim on trims on
alter session set nls_Date_format='DD/MON/YYYY HH24:MI:SS';
select b.sid,b.serial#,substr(b.program,1,12) pgm,a.spid,b.process,b.status,b.sql_id,b.prev_sql_id,b.logon_time,b.sql_exec_start,b.prev_exec_start from v$session b,v$process a where a.addr=b.paddr and b.username='INVENTORYADM' order by 1;
Query op:
DB Side:
1* select b.sid,b.serial#,substr(b.program,1,12) pgm,a.spid,b.process,b.status,b.sql_id,b.prev_sql_id,b.logon_time,b.sql_exec_start,b.prev_exec_start from v$session b,v$process a where a.addr=b.paddr and b.username='INVENTORYADM' order by b.program,b.sid
17:03:25 SQL> /
SID, SERIAL#,PGM ,SPID ,PROCESS ,STATUS ,SQL_ID ,PREV_SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PREV_EXEC_START
----------,----------,------------------------------------------------,------------------------,------------------------,--------,-------------,-------------,--------------------,--------------------,--------------------
125, 24667,DBeaver 23?0 ,6768 ,1234 ,KILLED , ,b3s1x9zqrvzvc,21/APR/2024 17:00:57, ,21/APR/2024 17:00:57
138, 15750,DBeaver 23?0 ,6770 ,1234 ,KILLED , ,73b7u0bsntwxp,21/APR/2024 17:00:57, ,21/APR/2024 17:01:13
69, 11844,DBeaver 23?0 ,6772 ,1234 ,KILLED , ,5rsm4y10jd4p2,21/APR/2024 17:00:57, ,21/APR/2024 17:01:14
133, 30476,sqlplus@vcen ,6798 ,6793 ,KILLED , ,g4y6nw3tts7cc,21/APR/2024 17:01:09, ,21/APR/2024 17:01:08
Elapsed: 00:00:00.01
4. Acknowledge the session killed from client (just try referesh the connection and notice what happens)
client:
[oracle@vcentos79-oracle-sa1 ~]$ sqlplus inventoryadm@127.0.0.1:1521/INVENTORYPDB
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 21 17:01:04 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Enter password:
Last Successful login time: Sun Apr 21 2024 17:00:57 +01:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select sysdate from dual;
select sysdate from dual
*
ERROR at line 1:
ORA-02396: exceeded maximum idle time, please connect again
SQL> exit
ERROR:
ORA-01012: not logged on
Process ID: 0
Session ID: 133 Serial number: 30476
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0 (with complications)
As you refresh individual sections of dbeaver, the sessions will be lost one after the other in v$session.
5. Check in the db what happened to the session
Output:
1* select b.sid,b.serial#,substr(b.program,1,12) pgm,a.spid,b.process,b.status,b.sql_id,b.prev_sql_id,b.logon_time,b.sql_exec_start,b.prev_exec_start from v$session b,v$process a where a.addr=b.paddr and b.username='INVENTORYADM' order by b.program,b.sid
17:03:25 SQL> /
SID, SERIAL#,PGM ,SPID ,PROCESS ,STATUS ,SQL_ID ,PREV_SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PREV_EXEC_START
----------,----------,------------------------------------------------,------------------------,------------------------,--------,-------------,-------------,--------------------,--------------------,--------------------
125, 24667,DBeaver 23?0 ,6768 ,1234 ,KILLED , ,b3s1x9zqrvzvc,21/APR/2024 17:00:57, ,21/APR/2024 17:00:57
138, 15750,DBeaver 23?0 ,6770 ,1234 ,KILLED , ,73b7u0bsntwxp,21/APR/2024 17:00:57, ,21/APR/2024 17:01:13
69, 11844,DBeaver 23?0 ,6772 ,1234 ,KILLED , ,5rsm4y10jd4p2,21/APR/2024 17:00:57, ,21/APR/2024 17:01:14
133, 30476,sqlplus@vcen ,6798 ,6793 ,KILLED , ,g4y6nw3tts7cc,21/APR/2024 17:01:09, ,21/APR/2024 17:01:08
Elapsed: 00:00:00.01
17:03:26 SQL> /
SID, SERIAL#,PGM ,SPID ,PROCESS ,STATUS ,SQL_ID ,PREV_SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PREV_EXEC_START
----------,----------,------------------------------------------------,------------------------,------------------------,--------,-------------,-------------,--------------------,--------------------,--------------------
125, 24667,DBeaver 23?0 ,6768 ,1234 ,KILLED , ,b3s1x9zqrvzvc,21/APR/2024 17:00:57, ,21/APR/2024 17:00:57
138, 15750,DBeaver 23?0 ,6770 ,1234 ,KILLED , ,73b7u0bsntwxp,21/APR/2024 17:00:57, ,21/APR/2024 17:01:13
69, 11844,DBeaver 23?0 ,6772 ,1234 ,KILLED , ,5rsm4y10jd4p2,21/APR/2024 17:00:57, ,21/APR/2024 17:01:14
Elapsed: 00:00:00.00
17:03:55 SQL> !ps -ef|grep -E "6768|6770|6772|6798"
oracle 6768 1 0 17:00 ? 00:00:00 oracleORA19C (LOCAL=NO)
oracle 6770 1 0 17:00 ? 00:00:00 oracleORA19C (LOCAL=NO)
oracle 6772 1 0 17:00 ? 00:00:00 oracleORA19C (LOCAL=NO)
oracle 6980 4568 0 17:04 pts/0 00:00:00 /bin/bash -c ps -ef|grep -E "6768|6770|6772|6798"
oracle 6982 6980 0 17:04 pts/0 00:00:00 grep -E 6768|6770|6772|6798
17:04:20 SQL> /
SID, SERIAL#,PGM ,SPID ,PROCESS ,STATUS ,SQL_ID ,PREV_SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PREV_EXEC_START
----------,----------,------------------------------------------------,------------------------,------------------------,--------,-------------,-------------,--------------------,--------------------,--------------------
125, 24667,DBeaver 23?0 ,6768 ,1234 ,KILLED , ,b3s1x9zqrvzvc,21/APR/2024 17:00:57, ,21/APR/2024 17:00:57
138, 15750,DBeaver 23?0 ,6770 ,1234 ,KILLED , ,73b7u0bsntwxp,21/APR/2024 17:00:57, ,21/APR/2024 17:01:13
Elapsed: 00:00:00.00
17:04:39 SQL> /
SID, SERIAL#,PGM ,SPID ,PROCESS ,STATUS ,SQL_ID ,PREV_SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PREV_EXEC_START
----------,----------,------------------------------------------------,------------------------,------------------------,--------,-------------,-------------,--------------------,--------------------,--------------------
125, 24667,DBeaver 23?0 ,6768 ,1234 ,KILLED , ,b3s1x9zqrvzvc,21/APR/2024 17:00:57, ,21/APR/2024 17:00:57
Elapsed: 00:00:00.01
17:04:58 SQL> /
No comments:
Post a Comment