Oracle RDBMS - How to control connection count or manage db connection in oracle database using DB user profile - what happens to the session in KILLED state

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> /

YouTube:

Pod Cast: https://podcasters.spotify.com/pod/show/vadivelraja-s/episodes/Oracle-dba_profiles-resource-limit-idle_time-what-happens-to-KILLED-session-e2im604

Oracle RDBMS - How to control connection count or manage db connection in oracle database using DB user profile

 

Lab Objective: How to control connection count or manage db connection in oracle database using DB user profile

reference url: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-PROFILE.html

Steps:

1. Examine the existing profile to which your candidate user is assiged and its limit

set lines 1200 pages 40000 colsep , time on timing on trim on trims on

col username for a20

col profile for a30

col INITIAL_RSRC_CONSUMER_GROUP for a30

col last_login for a40

col profile for a40

col LIMIT for a40

alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';

select username

,account_Status

,created

,last_login

,profile

,INITIAL_RSRC_CONSUMER_GROUP

from dba_users

where oracle_maintained='N'

order by username;

select *

from dba_profiles

where profile='DEFAULT'

order by resource_name;

snippet from reference:

-- Ideally you would see the IDLE_TIME parameter set to UNLIMITED, when you update this value to some numeral, then the session staying in idle state is expected to be terminated after the set number.

-- The client will get to know that the session is terminated only when they try run the command again

Notice here there is one more limit INACTIVE_ACCOUNT_TIME which looks for the user last login and locks it as per the setting.

Output:

12:30:10 SQL> select username ,account_Status ,created ,last_login ,profile ,INITIAL_RSRC_CONSUMER_GROUP from dba_users where oracle_maintained='N' order by username;12:30:14   2  12:30:14   3  12:30:14   4  12:30:14   5  12:30:14   6  12:30:14   7  12:30:14   8  12:30:14   9 USERNAME            ,ACCOUNT_STATUS                  ,CREATED             ,LAST_LOGIN                              ,PROFILE                                 ,INITIAL_RSRC_CONSUMER_GROUP --------------------,--------------------------------,--------------------,----------------------------------------,----------------------------------------,------------------------------ C##GGADMIN          ,OPEN                            ,13/JUN/2023 21:50:44,13-JUN-23 10.14.52.000000000 PM +01:00  ,DEFAULT                                 ,DEFAULT_CONSUMER_GROUP INVENTORYADM        ,OPEN                            ,19/APR/2023 20:49:24,20-APR-24 11.02.05.000000000 AM +01:00  ,DEFAULT                                 ,DEFAULT_CONSUMER_GROUP ORDS_METADATA       ,OPEN                            ,22/APR/2023 20:18:05,                                        ,DEFAULT                                 ,DEFAULT_CONSUMER_GROUP ORDS_PUBLIC_USER    ,OPEN                            ,22/APR/2023 20:18:03,02-MAY-23 11.02.38.000000000 PM +01:00  ,DEFAULT                                 ,DEFAULT_CONSUMER_GROUP SCH_HELLOWORLD      ,OPEN                            ,02/MAY/2023 22:16:26,                                        ,DEFAULT                                 ,DEFAULT_CONSUMER_GROUP Elapsed: 00:00:00.01 12:30:15 SQL> select * from dba_profiles where profile='DEFAULT' order by resource_name;12:31:03   2  12:31:03   3  12:31:03   4 PROFILE                                 ,RESOURCE_NAME                   ,RESOURCE,LIMIT                                   ,COM,INH,IMP ----------------------------------------,--------------------------------,--------,----------------------------------------,---,---,--- DEFAULT                                 ,COMPOSITE_LIMIT                 ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO DEFAULT                                 ,CONNECT_TIME                    ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO DEFAULT                                 ,CPU_PER_CALL                    ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO DEFAULT                                 ,CPU_PER_SESSION                 ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO DEFAULT                                 ,FAILED_LOGIN_ATTEMPTS           ,PASSWORD,10                                      ,NO ,NO ,NO DEFAULT                                 ,IDLE_TIME                       ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO DEFAULT                                 ,INACTIVE_ACCOUNT_TIME           ,PASSWORD,UNLIMITED                               ,NO ,NO ,NO DEFAULT                                 ,LOGICAL_READS_PER_CALL          ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO DEFAULT                                 ,LOGICAL_READS_PER_SESSION       ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO DEFAULT                                 ,PASSWORD_GRACE_TIME             ,PASSWORD,7                                       ,NO ,NO ,NO DEFAULT                                 ,PASSWORD_LIFE_TIME              ,PASSWORD,UNLIMITED                               ,NO ,NO ,NO DEFAULT                                 ,PASSWORD_LOCK_TIME              ,PASSWORD,1                                       ,NO ,NO ,NO DEFAULT                                 ,PASSWORD_REUSE_MAX              ,PASSWORD,UNLIMITED                               ,NO ,NO ,NO DEFAULT                                 ,PASSWORD_REUSE_TIME             ,PASSWORD,UNLIMITED                               ,NO ,NO ,NO DEFAULT                                 ,PASSWORD_VERIFY_FUNCTION        ,PASSWORD,NULL                                    ,NO ,NO ,NO DEFAULT                                 ,PRIVATE_SGA                     ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO DEFAULT                                 ,SESSIONS_PER_USER               ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO 17 rows selected. Elapsed: 00:00:00.01 12:31:04 SQL>

2. Remember updating the existing profile is going to change the profile limit for all the other users who are assigned this profile, so it is better to create a new profile as a clone of existing and update the new profile with the setting needed. 

set long 20000

select dbms_metadata.get_ddl('PROFILE','DEFAULT') from dual;

modify alter statement with create statement...

   CREATE PROFILE "PROF_IDLECNTL"

    LIMIT

         COMPOSITE_LIMIT UNLIMITED

         SESSIONS_PER_USER UNLIMITED

         CPU_PER_SESSION UNLIMITED

         CPU_PER_CALL UNLIMITED

         LOGICAL_READS_PER_SESSION UNLIMITED

         LOGICAL_READS_PER_CALL UNLIMITED

         IDLE_TIME 2

         CONNECT_TIME UNLIMITED

         PRIVATE_SGA UNLIMITED

         FAILED_LOGIN_ATTEMPTS 10

         PASSWORD_LIFE_TIME UNLIMITED

         PASSWORD_REUSE_TIME UNLIMITED

         PASSWORD_REUSE_MAX UNLIMITED

         PASSWORD_VERIFY_FUNCTION NULL

         PASSWORD_LOCK_TIME 86400/86400

         PASSWORD_GRACE_TIME 604800/86400

         INACTIVE_ACCOUNT_TIME UNLIMITED;

select * from dba_profiles where profile='&profname' order by resource_name;

Output:

1* select dbms_metadata.get_ddl('PROFILE','DEFAULT') from dual 12:35:05 SQL> / DBMS_METADATA.GET_DDL('PROFILE','DEFAULT') --------------------------------------------------------------------------------    ALTER PROFILE "DEFAULT"     LIMIT          COMPOSITE_LIMIT UNLIMITED          SESSIONS_PER_USER UNLIMITED          CPU_PER_SESSION UNLIMITED          CPU_PER_CALL UNLIMITED          LOGICAL_READS_PER_SESSION UNLIMITED          LOGICAL_READS_PER_CALL UNLIMITED          IDLE_TIME UNLIMITED          CONNECT_TIME UNLIMITED          PRIVATE_SGA UNLIMITED          FAILED_LOGIN_ATTEMPTS 10          PASSWORD_LIFE_TIME UNLIMITED          PASSWORD_REUSE_TIME UNLIMITED          PASSWORD_REUSE_MAX UNLIMITED          PASSWORD_VERIFY_FUNCTION NULL          PASSWORD_LOCK_TIME 86400/86400          PASSWORD_GRACE_TIME 604800/86400          INACTIVE_ACCOUNT_TIME UNLIMITED Elapsed: 00:00:00.06 12:35:06 SQL> 12:35:06 SQL>    CREATE PROFILE "PROF_IDLECNTL" 12:36:31   2      LIMIT          COMPOSITE_LIMIT UNLIMITED          SESSIONS_PER_USER UNLIMITED          CPU_PER_SESSION UNLIMITED          CPU_PER_CALL UNLIMITED          LOGICAL_READS_PER_SESSION UNLIMITED          LOGICAL_READS_PER_CALL UNLIMITED 12:36:31   3  12:36:31   4  12:36:31   5  12:36:31   6  12:36:31   7  12:36:31   8  12:36:31   9           IDLE_TIME 2          CONNECT_TIME UNLIMITED          PRIVATE_SGA UNLIMITED          FAILED_LOGIN_ATTEMPTS 10          PASSWORD_LIFE_TIME UNLIMITED          PASSWORD_REUSE_TIME UNLIMITED          PASSWORD_REUSE_MAX UNLIMITED          PASSWORD_VERIFY_FUNCTION NULL          PASSWORD_LOCK_TIME 86400/86400          PASSWORD_GRACE_TIME 604800/86400          INACTIVE_ACCOUNT_TIME UNLIMITED;12:36:31  10  12:36:31  11  12:36:31  12  12:36:31  13  12:36:31  14  12:36:31  15  12:36:31  16  12:36:31  17  12:36:31  18  12:36:31  19 Profile created. Elapsed: 00:00:00.01 12:36:31 SQL> select * from dba_profiles where profile='&profname' order by resource_name; Enter value for profname: PROF_IDLECNTL old   1: select * from dba_profiles where profile='&profname' order by resource_name new   1: select * from dba_profiles where profile='PROF_IDLECNTL' order by resource_name PROFILE                                 ,RESOURCE_NAME                   ,RESOURCE,LIMIT                                   ,COM,INH,IMP ----------------------------------------,--------------------------------,--------,----------------------------------------,---,---,--- PROF_IDLECNTL                           ,COMPOSITE_LIMIT                 ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO PROF_IDLECNTL                           ,CONNECT_TIME                    ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO PROF_IDLECNTL                           ,CPU_PER_CALL                    ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO PROF_IDLECNTL                           ,CPU_PER_SESSION                 ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO PROF_IDLECNTL                           ,FAILED_LOGIN_ATTEMPTS           ,PASSWORD,10                                      ,NO ,NO ,NO PROF_IDLECNTL                           ,IDLE_TIME                       ,KERNEL  ,2                                       ,NO ,NO ,NO PROF_IDLECNTL                           ,INACTIVE_ACCOUNT_TIME           ,PASSWORD,UNLIMITED                               ,NO ,NO ,NO PROF_IDLECNTL                           ,LOGICAL_READS_PER_CALL          ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO PROF_IDLECNTL                           ,LOGICAL_READS_PER_SESSION       ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO PROF_IDLECNTL                           ,PASSWORD_GRACE_TIME             ,PASSWORD,7                                       ,NO ,NO ,NO PROF_IDLECNTL                           ,PASSWORD_LIFE_TIME              ,PASSWORD,UNLIMITED                               ,NO ,NO ,NO PROF_IDLECNTL                           ,PASSWORD_LOCK_TIME              ,PASSWORD,1                                       ,NO ,NO ,NO PROF_IDLECNTL                           ,PASSWORD_REUSE_MAX              ,PASSWORD,UNLIMITED                               ,NO ,NO ,NO PROF_IDLECNTL                           ,PASSWORD_REUSE_TIME             ,PASSWORD,UNLIMITED                               ,NO ,NO ,NO PROF_IDLECNTL                           ,PASSWORD_VERIFY_FUNCTION        ,PASSWORD,NULL                                    ,NO ,NO ,NO PROF_IDLECNTL                           ,PRIVATE_SGA                     ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO PROF_IDLECNTL                           ,SESSIONS_PER_USER               ,KERNEL  ,UNLIMITED                               ,NO ,NO ,NO 17 rows selected.

3. Assign the profile to the candidate user

alter user INVENTORYADM profile &profname;

Output:

12:38:01 SQL> alter user INVENTORYADM profile &profname; Enter value for profname: PROF_IDLECNTL old   1: alter user INVENTORYADM profile &profname new   1: alter user INVENTORYADM profile PROF_IDLECNTL User altered. Elapsed: 00:00:00.00 12:38:11 SQL> 12:38:11 SQL> select username ,account_Status ,created ,last_login ,profile ,INITIAL_RSRC_CONSUMER_GROUP from dba_users where oracle_maintained='N' order by username; 12:38:58   2  12:38:58   3  12:38:58   4  12:38:58   5  12:38:58   6  12:38:58   7  12:38:58   8  12:38:58   9 USERNAME            ,ACCOUNT_STATUS                  ,CREATED             ,LAST_LOGIN                              ,PROFILE                                 ,INITIAL_RSRC_CONSUMER_GROUP --------------------,--------------------------------,--------------------,----------------------------------------,----------------------------------------,------------------------------ C##GGADMIN          ,OPEN                            ,13/JUN/2023 21:50:44,13-JUN-23 10.14.52.000000000 PM +01:00  ,DEFAULT                                 ,DEFAULT_CONSUMER_GROUP INVENTORYADM        ,OPEN                            ,19/APR/2023 20:49:24,20-APR-24 11.02.05.000000000 AM +01:00  ,PROF_IDLECNTL                           ,DEFAULT_CONSUMER_GROUP ORDS_METADATA       ,OPEN                            ,22/APR/2023 20:18:05,                                        ,DEFAULT                                 ,DEFAULT_CONSUMER_GROUP ORDS_PUBLIC_USER    ,OPEN                            ,22/APR/2023 20:18:03,02-MAY-23 11.02.38.000000000 PM +01:00  ,DEFAULT                                 ,DEFAULT_CONSUMER_GROUP SCH_HELLOWORLD      ,OPEN                            ,02/MAY/2023 22:16:26,                                        ,DEFAULT                                 ,DEFAULT_CONSUMER_GROUP Elapsed: 00:00:00.01 12:38:58 SQL>

4. Test if the user session gets terminated

-> login

> use dbeaver to connect to the user

-> run a test query

> open sql editor

SELECT object_name FROM user_objects;

-> check in v$session the session details

select sid,serial#,username,status,state,sql_id,program,logon_time from v$session where username='&usr';

-> leave the session for "2 mins" idle time

-> see if the session is terminated

a. check v$session

select sid,serial#,username,status,state,sql_id,program,logon_time from v$session where username='&usr';

b. try query from the program where user is connected for test and see the response

SELECT object_name FROM user_objects;

Output:

12:38:58 SQL> select sid,serial#,username,status,state,sql_id,program,logon_time from v$session where username='&usr'; Enter value for usr: INVENTORYADM old   1: select sid,serial#,username,status,state,sql_id,program,logon_time from v$session where username='&usr' new   1: select sid,serial#,username,status,state,sql_id,program,logon_time from v$session where username='INVENTORYADM'        SID,   SERIAL#,USERNAME            ,STATUS  ,STATE              ,SQL_ID       ,PROGRAM                                         ,LOGON_TIME ----------,----------,--------------------,--------,-------------------,-------------,------------------------------------------------,--------------------         63,     48434,INVENTORYADM        ,INACTIVE,WAITING            ,             ,DBeaver 23?0?1 ? Metadata                       ,20/APR/2024 12:39:34        112,     46550,INVENTORYADM        ,INACTIVE,WAITING            ,             ,DBeaver 23?0?1 ? Main                           ,20/APR/2024 12:39:33        127,     40145,INVENTORYADM        ,INACTIVE,WAITING            ,5rsm4y10jd4p2,DBeaver 23?0?1 ? SQLEditor ?Script?sql?         ,20/APR/2024 12:39:34 Elapsed: 00:00:00.00 12:40:27 SQL> select sql_text from v$sqltext where sql_id='5rsm4y10jd4p2' order by piece; SQL_TEXT ---------------------------------------------------------------- BEGIN DBMS_OUTPUT.GET_LINE(:1 , :2 ); END; Elapsed: 00:00:00.02 12:40:45 SQL> select sid,serial#,username,status,state,sql_id,program,logon_time,prev_Sql_id from v$session where username='&usr'; Enter value for usr: INVENTORYADM old   1: select sid,serial#,username,status,state,sql_id,program,logon_time,prev_Sql_id from v$session where username='&usr' new   1: select sid,serial#,username,status,state,sql_id,program,logon_time,prev_Sql_id from v$session where username='INVENTORYADM'        SID,   SERIAL#,USERNAME            ,STATUS  ,STATE              ,SQL_ID       ,PROGRAM                                         ,LOGON_TIME          ,PREV_SQL_ID ----------,----------,--------------------,--------,-------------------,-------------,------------------------------------------------,--------------------,-------------         63,     48434,INVENTORYADM        ,INACTIVE,WAITING            ,             ,DBeaver 23?0?1 ? Metadata                       ,20/APR/2024 12:39:34,73b7u0bsntwxp        112,     46550,INVENTORYADM        ,INACTIVE,WAITING            ,             ,DBeaver 23?0?1 ? Main                           ,20/APR/2024 12:39:33,5qus23w3vf7ry        127,     40145,INVENTORYADM        ,INACTIVE,WAITING            ,5rsm4y10jd4p2,DBeaver 23?0?1 ? SQLEditor ?Script?sql?         ,20/APR/2024 12:39:34,5rsm4y10jd4p2 Elapsed: 00:00:00.02 12:41:02 SQL> select sql_text from v$sqltext where sql_id='5qus23w3vf7ry' order by piece; SQL_TEXT ---------------------------------------------------------------- SELECT SYS_CONTEXT( 'USERENV', 'CURRENT_SCHEMA' ) FROM DUAL Elapsed: 00:00:00.00 12:41:13 SQL> select sql_text from v$sqltext where sql_id='73b7u0bsntwxp' order by piece; SQL_TEXT ---------------------------------------------------------------- SELECT  O.*, t.TABLE_TYPE_OWNER,t.TABLE_TYPE,t.TABLESPACE_NAME,t .PARTITIONED,t.IOT_TYPE,t.IOT_NAME,t.TEMPORARY,t.SECONDARY,t.NES TED,t.NUM_ROWS FROM ALL_OBJECTS O , ALL_ALL_TABLES t WHERE t.OWN ER(+) = O.OWNER AND t.TABLE_NAME(+) = o.OBJECT_NAME AND O.OWNER= :1  AND O.OBJECT_TYPE IN ('TABLE', 'VIEW', 'MATERIALIZED VIEW') AND O.OBJECT_NAME=:2 6 rows selected. Elapsed: 00:00:00.00 12:41:20 SQL> select sid,serial#,username,status,state,sql_id,program,logon_time,prev_Sql_id from v$session where username='INVENTORYADM';        SID,   SERIAL#,USERNAME            ,STATUS  ,STATE              ,SQL_ID       ,PROGRAM                                         ,LOGON_TIME          ,PREV_SQL_ID ----------,----------,--------------------,--------,-------------------,-------------,------------------------------------------------,--------------------,-------------         63,     48434,INVENTORYADM        ,INACTIVE,WAITING            ,             ,DBeaver 23?0?1 ? Metadata                       ,20/APR/2024 12:39:34,73b7u0bsntwxp        112,     46550,INVENTORYADM        ,INACTIVE,WAITING            ,             ,DBeaver 23?0?1 ? Main                           ,20/APR/2024 12:39:33,5qus23w3vf7ry        127,     40145,INVENTORYADM        ,INACTIVE,WAITING            ,5rsm4y10jd4p2,DBeaver 23?0?1 ? SQLEditor ?Script?sql?         ,20/APR/2024 12:39:34,5rsm4y10jd4p2 Elapsed: 00:00:00.01 12:42:15 SQL> / ---> idle for 2 mins ---> session terminated: 12:42:15 SQL> /        SID,   SERIAL#,USERNAME            ,STATUS  ,STATE              ,SQL_ID       ,PROGRAM                                         ,LOGON_TIME          ,PREV_SQL_ID ----------,----------,--------------------,--------,-------------------,-------------,------------------------------------------------,--------------------,-------------         63,     48434,INVENTORYADM        ,KILLED  ,WAITED KNOWN TIME  ,             ,DBeaver 23?0?1 ? Metadata                       ,20/APR/2024 12:39:34,73b7u0bsntwxp        112,     46550,INVENTORYADM        ,KILLED  ,WAITED KNOWN TIME  ,             ,DBeaver 23?0?1 ? Main                           ,20/APR/2024 12:39:33,5qus23w3vf7ry        127,     40145,INVENTORYADM        ,KILLED  ,WAITED KNOWN TIME  ,             ,DBeaver 23?0?1 ? SQLEditor ?Script?sql?         ,20/APR/2024 12:39:34,5rsm4y10jd4p2 Elapsed: 00:00:00.00 12:42:41 SQL> /

---> it stays in killed state

alert log:

2024-04-20 12:42:17.730000 +01:00

KILL SESSION for sid=(63, 48434):

  Reason = profile limit idle_time

  Mode = KILL SOFT -/-/NO_REPLAY

  Requestor = PMON (orapid = 2, ospid = 11375, inst = 1)

  Owner = Process: USER (orapid = 52, ospid = 18254)

  Result = ORA-0

KILL SESSION for sid=(112, 46550):

  Reason = profile limit idle_time

  Mode = KILL SOFT -/-/NO_REPLAY

  Requestor = PMON (orapid = 2, ospid = 11375, inst = 1)

  Owner = Process: USER (orapid = 51, ospid = 18252)

  Result = ORA-0

KILL SESSION for sid=(127, 40145):

  Reason = profile limit idle_time

  Mode = KILL SOFT -/-/NO_REPLAY

  Requestor = PMON (orapid = 2, ospid = 11375, inst = 1)

  Owner = Process: USER (orapid = 53, ospid = 18256)

  Result = ORA-0

Dbeaver screenshot post session termination:

YouTube Video: