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

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);

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


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');



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;

2 thoughts 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

  2. Pingback: How to Clean Inactive Oracle Sessions Automatically? | DB & LINUX & BI

Leave a Reply

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

You are commenting using your 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.