Views used for resource management:
V$RSRC_SESSION_INFO
V$RSRC_CONSUMER_GROUP
V$SESSION
DBA_USERS
DBA_RSRC_CONSUMER_GROUPS
DBA_RSRC_CONSUMER_GROUPS_PRIVS
DBA_RSRC_PLAN_DIRECTIVES --displays directives for existing plans
Create a consumer group and make it the default for a user
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_consumer_group(consumer_group => 'some_group', comment => '', cpu_mth => 'ROUND-ROBIN');
dbms_resource_manager.submit_pending_area();
END;
BEGIN
dbms_resource_manager_privs.grant_switch_consumer_group(
grantee_name => 'test',
consumer_group => 'some_group',
grant_option => FALSE
);
END;
BEGIN
dbms_resource_manager.set_initial_consumer_group(
user => 'test',
consumer_group => 'some_group'
);
END;
Create a plan and activate it:
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_plan( 'TEST_PLAN', '');
dbms_resource_manager.create_plan_directive(
plan => 'some plan',
group_or_subplan => 'TEST_GRP',
comment => '',
cpu_p1 => NULL, cpu_p2 => NULL, cpu_p3 => NULL, cpu_p4 => 100,
cpu_p5 => NULL, cpu_p6 => NULL, cpu_p7 => NULL, cpu_p8 => NULL,
parallel_degree_limit_p1 => NULL,
active_sess_pool_p1 => NULL,
queueing_p1 => NULL,
switch_group => 'some other low priority group',
switch_time => 600,
switch_estimate => false,
max_est_exec_time => NULL,
undo_pool => NULL,
max_idle_time => NULL,
max_idle_blocker_time => NULL,
switch_time_in_call => NULL
); -- add as many as required resource plans
dbms_resource_manager.submit_pending_area();
END;
alter system set resource_manager_plan='some plan' scope=both;
--
RaduStoica - 17 Oct 2007