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>
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>
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
No comments:
Post a Comment