Configure Resource Manager to kill INACTIVE SESSION in Oracle

Configure Resource Manager to kill INACTIVE SESSION in Oracle

1. Create a pending area for resource plan.

exec dbms_resource_manager.create_pending_area();

2. Create a plan for kill inactive session after crossed idle time.

--Check the name already exists
SELECT plan,status,comments FROM dba_rsrc_plans;


--Create the plan
exec dbms_resource_manager.create_plan( plan => 'Kill_INACTIVE_PLAN', comment => 'kill inactive sessions');


3. Create the consumer groups.

exec dbms_resource_manager.create_consumer_group( consumer_group => 'LONG_RUN',comment =>'Privileged Users');


exec dbms_resource_manager.create_consumer_group( consumer_group => 'SHORT_RUN',comment => 'Under Privileged Users');


4. Create directives for the plan in Resource manager
you can define directives which is used by different setting for configure different user to different directives that will use different time limit as following examples.

exec dbms_resource_manager.create_plan_directive( plan => 'Kill_INACTIVE_PLAN', group_or_subplan =>
'LONG_RUN', comment => '15 minutes', max_idle_time => 900);


exec dbms_resource_manager.create_plan_directive( plan => 'Kill_INACTIVE_PLAN', group_or_subplan =>
'SHORT_RUN', comment => '5 minute idle time', max_idle_time => 300);


exec dbms_resource_manager.create_plan_directive( plan => 'Kill_INACTIVE_PLAN', group_or_subplan =>
'OTHER_RUN', comment => '1 Hour ideal time', max_idle_time => 3600);


5. Submit the plan in Resource Manager.

exec dbms_resource_manager.validate_pending_area();
exec dbms_resource_manager.submit_pending_area();


6. consumer group switching



begin
dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name => 'SYSTEM',
consumer_group => 'LONG_RUN', grant_option => FALSE);
dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name => 'TEST',
consumer_group => 'SHORT_RUN', grant_option => FALSE);
end;
/




7. Change database users default consumer groups:
Assign user SYSTEM to the group LONG_RUN
Assign user TEST to the group SHORT_RUN


begin dbms_resource_manager.create_pending_area();

dbms_resource_manager.set_initial_consumer_group( user => 'SYSTEM',
consumer_group => 'LONG_RUN');
dbms_resource_manager.set_initial_consumer_group( user => 'TEST',
consumer_group => 'SHORT_RUN');

dbms_resource_manager.validate_pending_area();

dbms_resource_manager.submit_pending_area();
end;
/


8. Change resource plan for database and make it active.

alter system set resource_manager_plan='Kill_INACTIVE_PLAN';


9. Check query start using the new resource plan
SYSTEM user assigned to LONG_RUN and TEST user assigned to SHORT_RUN and rest user with OTHER_RUN

SELECT sid,serial#,username,resource_consumer_group FROM v$session;