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;

1 thought on “Configure Resource Manager to kill INACTIVE SESSION in Oracle

  1. Pingback: Cause of too many inactive session and clean them in Oracle | Smart way of Technology

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.