1. Home
  2. Knowledge Base
  3. Database Administration
  4. Using the 10g Resource Manager to manage INACTIVE sessions

Using the 10g Resource Manager to manage INACTIVE sessions

CREATE THE PLAN

 begin
dbms_resource_manager.create_pending_area();
end;
/
 begin
dbms_resource_manager.create_plan( plan => 'TEST_PLAN', comment => 'Resource plan/method for Idle
time kill sessions');
end;
/

CREATE THE CONSUMER GROUPS

begin
dbms_resource_manager.create_consumer_group( consumer_group => 'LONG_RUNNING',
comment =>'Privileged Users');
dbms_resource_manager.create_consumer_group( consumer_group => 'SHORT_RUNNING',
comment => 'Under Privileged Users');
end;
/

CREATE DIRECTIVES FOR THE PLAN

  • Consumer Group LONG_RUNNING will have idle time set to 5 MINUTES (300 seconds)
  • Consumer Group SHORT_RUNNING will have idle time set to 1 Minute (60 seconds)
  • Users falling in the Consumer Group OTHER_GROUPS will have idle time set to 1 Hour
  • begin
     dbms_resource_manager.create_plan_directive( plan => 'TEST_PLAN', group_or_subplan =>
    'LONG_RUNNING', comment => 'Limit idle time to 5 minutes', max_idle_time => 300);
    dbms_resource_manager.create_plan_directive( plan => 'TEST_PLAN', group_or_subplan =>
    'SHORT_RUNNING', comment => 'Limit idle time to 1 minute', max_idle_time => 60);
    dbms_resource_manager.create_plan_directive( plan => 'TEST_PLAN', group_or_subplan =>
     'OTHER_GROUPS', comment => 'Limit idle time to 1 Hour', max_idle_time => 3600);
     end;
     /
    

    VALIDATE AND SUBMIT THE PLAN

    begin
    dbms_resource_manager.validate_pending_area();
    end;
    /
    
    begin
    dbms_resource_manager.submit_pending_area();
    end;
    /
    

    ALLOW OR DISALLOW CONSUMER GROUP SWITCHING

    begin
    dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name => 'SYSTEM',
    consumer_group => 'LONG_RUNNING', grant_option => FALSE);
    dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name => 'SH',
    consumer_group => 'SHORT_RUNNING', grant_option => FALSE);
    end;
    /
    

    ASSIGN DATABASE USERS A DEFAULT INITIAL CONSUMER GROUP AT CONNECTION TIME

  • User SYSTEM is made member of the group LONG_RUNNING
  • User SH is made member of the group SHORT_RUNNING
  • 
    begin
    dbms_resource_manager.create_pending_area();
    end;
    /
    
    
    begin
    dbms_resource_manager.set_initial_consumer_group( user => 'SYSTEM',
    consumer_group => 'LONG_RUNNING');
    dbms_resource_manager.set_initial_consumer_group( user => 'SH',
    consumer_group => 'SHORT_RUNNING');
    end;
     /
    
    

    VALIDATE AND SUBMIT THE LAST ACTIONS MADE TO THE PLAN

     begin
    dbms_resource_manager.validate_pending_area();
    end;
    /
    
    
    begin
    dbms_resource_manager.submit_pending_area();
    end;
    /
    
    

    MAKE THE NEW PLAN THE ACTIVE ONE FOR THE DATABASE

    SQL> alter system set resource_manager_plan=’TEST_PLAN’;

    TEST

    This query will show the sessions that are killed when the idle time thresholds set for the plan are crossed.

     SQL> 	select NAME, ACTIVE_SESSIONS_KILLED, IDLE_SESSIONS_KILLED
    	FROM V$RSRC_CONSUMER_GROUP WHERE NAME='SHORT_RUNNING';
    
    NAME                             ACTIVE_SESSIONS_KILLED IDLE_SESSIONS_KILLED
    -------------------------------- ---------------------- --------------------
    SHORT_RUNNING                                         0                    0
    
    

    Initially this will show no sessions are killed

    Connect as user SH from another session

    
    apex:/u01/oracle/scripts> sqlplus sh/SH
    
    SQL*Plus: Release 11.1.0.6.0 - Production on Fri Sep 12 10:26:54 2008
    
    Copyright (c) 1982, 2007, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL>
    

    After ONE Minute has elapsed from the first session run the same query again

    SQL> 	select NAME, ACTIVE_SESSIONS_KILLED, IDLE_SESSIONS_KILLED
    	FROM V$RSRC_CONSUMER_GROUP WHERE NAME='SHORT_RUNNING';
    
    NAME                             ACTIVE_SESSIONS_KILLED IDLE_SESSIONS_KILLED
    -------------------------------- ---------------------- --------------------
    SHORT_RUNNING                                         0                    1
    

    It now shows that one session has been killed

    Confirm that the session has actually been killed

    SQL> quit
    ERROR:
    ORA-02396: exceeded maximum idle time, please connect again
    
    
    The following two tabs change content below.

    Gavin Soorma

    Latest posts by Gavin Soorma (see all)

    Updated on June 2, 2021

    Was this article helpful?

    Related Articles

    Leave a Comment