Sunday, November 29, 2020
dbametrix
More
    Home Oracle DBA Tips How to restrict Idle Users

    How to restrict Idle Users

    How to restrict idle user session when it is consuming memory from Oracle database.

    How to Restrict Idle user session?

    I have seen lots of cases in my experience. A bank officer opens so many sessions because he or she needs make entries in different kind of transactions. After that he or she leaves those sessions without doing any work. He/She may be enjoying lunch or tea or office break after living those sessions idle.  Idle session always consumes memory from Oracle memory region. Default it is unlimited then we should need to modify it. Let’s follow the steps.

    Step1:- connect as sysdba user

    - Advertisement -
    dbametrix

    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’);

    - Advertisement -
    dbametrix

    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.

    Consider Reading to these articles:

    - Advertisement -
    dbametrix
    - Advertisment -
    dbametrix

    Most Popular

    Oracle Scheduler Jobs Example

    Blog post explains how to configure job scheduler in Oracle by example with how to manage, delete, drop, disable and enable jobs with how to check history of job if broken and enable.

    Identifying important information in Big Data

    The way in which Big Data technologies have evolved in the real word enterprise goes on to show that even technologists and scientists who might have disparaged the word (Big Data) previously

    Five Reasons Why Database Outsourcing

    The blog post explains why database outsourcing and remote dba support becomes most popular during present time of Covid-19 Pandemic.

    Hardware of Database Server

    The blog post explains how to choose best hardware of your database server for getting high performance and security.

    Recent Comments