Database connections and controlling them - DB Profiles

 We often see developers coming to us and requesting that their session is stuck in the database and needs to be killed as they don't see them in their front end or they get involved in locking situation (in different context here w.r.to orphaned connections). While this may be general in non production environments, there are poorly designed production systems as well where the connections don't get closed properly or gracefully and left inside the database when the front end gets disconnected from the database. 

In this post, we will see how we can control the connections or sessions that gets connected to the database.

We will see the below method with some details, of course. 

• Database Profile

Database Profiles: 

The first and easy method is to create profile, which is a set of limits on database resources. If you assign the profile to a user, then that user cannot exceed these limits. 

We have to set the session related parameter idle_time or connect_time under that specific profile as limit, post the specified duration the session will be killed automatically by Oracle. 

Let's take an example by setting 2 minutes for the idle_time parameter. 

SQL> alter profile test limit IDLE_TIME 2;

Profile TEST altered.

SQL> alter user test profile test;

User TEST altered.

SQL> select * from dba_profiles where profile='TEST'
  2  and resource_name='IDLE_TIME';

   PROFILE    RESOURCE_NAME    RESOURCE_TYPE    LIMIT    COMMON    INHERITED    IMPLICIT
__________ ________________ ________________ ________ _________ ____________ ___________
TEST       IDLE_TIME        KERNEL           2        NO        NO           NO

SQL> sho user
USER is "SYSTEM"
SQL>

Now, we will connect to the DB using user TEST and leave the session idle for > 2 minutes and run a query again. Let's see what happens.

C:\Users\MJ>sql test/test@orcl

SQLcl: Release 21.1 Production on Sun Jul 04 20:56:23 2021

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


SQL> show user
USER is "TEST"
SQL> -- sitting idle for more than 2 minutes
SQL> select 1 from dual;

Error starting at line : 1 in command -
select 1 from dual
Error at Command Line : 1 Column : 1
Error report -
SQL Error: ORA-02396: exceeded maximum idle time, please connect again
02396. 00000 -  "exceeded maximum idle time, please connect again"
*Cause:    as stated
*Action:
SQL>

In the mean time we can see the below entries in the alert log

2021-07-04T20:59:44.952961+05:30
KILL SESSION for sid=(20, 8481):
  Reason = profile limit idle_time
  Mode = KILL SOFT -/-/-
  Requestor = PMON (orapid = 2, ospid = 2839, inst = 1)
  Owner = Process: USER (orapid = 44, ospid = 7137)
  Result = ORA-0

The existing connection will get the SQL Error: ORA-02396 only when the session tries to run the query from the session which was already killed automatically. 

Note, the session might take a minute or 2 to get killed post the idle_time specified in the profile. 

If you see the error like above, then we can conclude it's due to the profile settings. 

Since, most applications are designed very well to gracefully end the connection to the backend, we don't have to specify the connect_time limit, which will specify the total elapsed time for the session be it active or inactive unless you need aggressive control over the connections. If the parameter is set, oracle behaves the same killing session similar to idle_time parameter but the session will be terminated no matter you are idle or active. You can check this behavior as an exercise.. 

We will discuss about other option in next post here

Reference: 

CREATE PROFILE (oracle.com)

Courtesy: John Wick Reload GIF - JohnWick Reload OhBoyHereIGoKillingAgain - Discover & Share GIFs (tenor.com)

Happy Killing...!!!