Thursday, February 25, 2021
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

    Recent Comments