Saturday, March 2, 2013

Oracle - Resource Manager

Create a Resource Manager to kill a User after 15 min Ideal:

Example:
dbms_resource_manager.create_consumer_group
(consumer_group => 'IDEL_TIME_15',
new_comment => 'Disconnect the session after idle time is >15 minutes ',
new_cpu_mth => 'ROUND-ROBIN');
dbms_resource_manager.create_plan_directive(
    plan => 'TEST_2NODE_PLAN',group_or_subplan => 'IDEL_TIME_15',
    comment => '',
    cpu_p1 => NULL, cpu_p2 => NULL, cpu_p3 => NULL, cpu_p4 => NULL,
    cpu_p5 => NULL, cpu_p6 => NULL, cpu_p7 => 100, cpu_p8 => NULL,
    parallel_degree_limit_p1 => 4,
    active_sess_pool_p1 => NULL,
    queueing_p1 => NULL,
    switch_group => 'CANCEL_SQL',
    switch_time => NULL,
    switch_estimate => false,
    max_est_exec_time => NULL,
    undo_pool => NULL,
    max_idle_time => 1800,
    max_idle_blocker_time => UNLIMITED,
    switch_time_in_call => NULL
);
dbms_resource_manager.set_consumer_group_mapping(dbms_resource_manager.oracle_user,'sandeep','idel_time_15');
dbms_resource_manager.set_consumer_group_mapping(dbms_resource_manager.oracle_user,'jaydeep','idel_time_15');
dbms_resource_manager_privs.grant_switch_consumer_group('anujka','idel_time_15',false);
dbms_resource_manager_privs.grant_switch_consumer_group('jaydeep','idel_time_15',false);
dbms_resource_manager.submit_pending_area();
END;
/

No comments:

Post a Comment