How to Restrict Idle User Sessions?
I have seen lots of cases in my experience. A bank officer opens so many sessions because he or she needs to make entries in different kinds of transactions. After that, he or she leaves those sessions without doing any work. He/She may be enjoying lunch or tea or an office break after living those sessions idle. An idle session always consumes memory from the Oracle memory region. Default it is unlimited, then we should need to modify it. Let’s follow the steps.
Step1:- connect as sysdba user
SQL> conn /as sysdba
SQL> show parameter resource_limit
NAME TYPE VALUE
———————————— ———– ——————————
resource_limit boolean FALSE
SQL> conn /as sysdba
SQL> show parameter resource_limit
NAME TYPE VALUE
———————————— ———– ——————————
resource_limit boolean FALSE
Step2:- Change the resource parameter to true
SQL> alter system set resource_limit=TRUE scope=both;
System altered.
SQL> select profile,resource_name,limit from dba_profiles where profile='DEFAULT' and resource_name in (‘IDLE_TIME’,’CONNECT_TIME’);
PROFILE RESOURCE_NAME LIMIT
—————————— ——————————– ——– —————————————-
DEFAULT IDLE_TIME UNLIMITED
DEFAULT CONNECT_TIME UNLIMITED
SQL>
SQL> alter system set resource_limit=TRUE scope=both;
System altered.
SQL> select profile,resource_name,limit from dba_profiles where profile='DEFAULT' and resource_name in (‘IDLE_TIME’,’CONNECT_TIME’);
PROFILE RESOURCE_NAME LIMIT
—————————— ——————————– ——– —————————————-
DEFAULT IDLE_TIME UNLIMITED
DEFAULT CONNECT_TIME UNLIMITED
SQL>
Step3:- Change the IDLE time to 90min
SQL> ALTER PROFILE DEFAULT LIMIT IDLE_TIME 90;
Profile altered.
SQL> select profile,resource_name,limit from dba_profiles where profile=’DEFAULT’ and resource_name in (‘IDLE_TIME’,’CONNECT_TIME’);
PROFILE RESOURCE_NAME LIMIT
—————————— ——————————– ——–
DEFAULT IDLE_TIME 90
DEFAULT CONNECT_TIME UNLIMITED
SQL>
SQL> ALTER PROFILE DEFAULT LIMIT IDLE_TIME 90;
Profile altered.
SQL> select profile,resource_name,limit from dba_profiles where profile=’DEFAULT’ and resource_name in (‘IDLE_TIME’,’CONNECT_TIME’);
PROFILE RESOURCE_NAME LIMIT
—————————— ——————————– ——–
DEFAULT IDLE_TIME 90
DEFAULT CONNECT_TIME UNLIMITED
SQL>
It is changed to 90 min of idle time. The application will disconnect if it is idle for 90. Now if any user sessions are idle for more than 90 minutes then it will be terminated automatically. You can change this value as per your requirements.
When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert.
This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.