remote dba support
More
    HomeOracle DBA TipsHow 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 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

    - 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.

    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.

    - Advertisement -
    dbametrix
    - Advertisment -
    remote dba services

    Most Popular