使用dbms_resource_manager包来管理resource
要点：
dbms_resource_manager.clear_pending_area;
.create_pending_area
.submit_pending_area
.create_plan
.create_plan_directive
.set_consumer_group_mapping
.grant_switch_consumer_group

相关视图：
dba_rsrc_plans
dba_rsrc_consumer_groups
v$rsrc_plan

详情：

------------------------------------ 创建计划 创建计划指导 开始 -------------------
-- 创建编辑区
exec dbms_resource_manager.clear_pending_area;
exec dbms_resource_manager.create_pending_area;
exec dbms_resource_manager.create_plan('PROD_PLAN','');

-- 当前系统中的计划
select plan from dba_rsrc_plans;

-- 当前系统中的消费组
select consumer_group from dba_rsrc_consumer_groups;

-- 删除一个计划
exec dbms_resource_manager.delete_plan('prod_plan');

-- 添加计划指导
begin
dbms_resource_manager.create_plan_directive(
plan=> 'prod_plan',
group_or_subplan=> 'sys_group',
comment=>'',
cpu_p1=>100
);

dbms_resource_manager.create_plan_directive(
plan=> 'prod_plan',
group_or_subplan=> 'other_groups',
comment=>'',
cpu_p2=>100
);

dbms_resource_manager.create_plan_directive(
plan=> 'prod_plan',
group_or_subplan=> 'low_group',
comment=>'',
cpu_p3=>100,
active_sess_pool_p1 => 10,
queueing_p1 => 600,
max_idle_time => 300
);

end;

-- 提交编辑区
exec dbms_resource_manager.submit_pending_area();
------------------------------------ 创建计划 创建计划指导 结束 ----------------------

------------------------------------ 创建消费组 修改计划指导 开始 --------------------
2. Add the APPUSER consumer group to the PROD_PLAN resource plan. Change
the level 3 CPU resource allocation percentages: 60% for the APPUSER
consumer group and 40% for the LOW_GROUP consumer group.

-- 创建一个消费组
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_consumer_group(consumer_group => 'APPUSER', comment => '');
dbms_resource_manager.submit_pending_area();

dbms_resource_manager.update_plan_directive(
plan => 'PROD_PLAN',
group_or_subplan => 'LOW_GROUP',
new_comment => '',
new_cpu_p3 => 40
);

dbms_resource_manager.create_plan_directive(
plan => 'PROD_PLAN',
group_or_subplan => 'APPUSER',
comment => '',
cpu_p3 => 60
);

------------------------------------ 创建消费组 修改计划指导 结束 --------------------

------------------------------------ 创建消费组映射 开始 -----------------------------
------------------
Configure Consumer Group Mappings, so that the SH Oracle user belongs to the APPUSER consumer group,
and the HR Oracle user belongs to the LOW_GROUP consumer group.
Cofirm that ORACLE_USER attribute has a higher priority than the CLIENT_OS_USER attribute in the mapping.

dbms_resource_manager.set_consumer_group_mapping(
'ORACLE_USER',
'SH',
'APPUSER'
);
dbms_resource_manager.set_consumer_group_mapping(
'ORACLE_USER',
'HR',
'LOW_GROUP'
);

------------------
Configure Consumer Group Mappings, so that the oracle OS user belongs to the
SYS_GROUP consumer group.

dbms_resource_manager.set_consumer_group_mapping('CLIENT_OS_USER','oracle','SYS_GROUP');

------------------
Assign the OE Oracle user to the following consumer groups: APPUSER,
LOW_GROUP, and SYS_GROUP.

dbms_resource_manager_privs.grant_switch_consumer_group(
grantee_name => 'OE',
consumer_group => 'APPUSER',
grant_option => FALSE
);
dbms_resource_manager_privs.grant_switch_consumer_group('OE', 'LOW_GROUP',FALSE);
dbms_resource_manager_privs.grant_switch_consumer_group('OE', 'SYS_GROUP',FALSE);

------------------
Activate the PROD_PLAN resource group.
dbms_resource_manager.switch_plan( plan_name => 'PROD_PLAN', sid => 'PROD' );

SQL> select * from v$rsrc_plan;

ID NAME IS_TO
---------- -------------------------------- -----
50377 PROD_PLAN TRUE

--------------- 反向操作 开始--------------
exec dbms_resource_manager.switch_plan( plan_name => 'SYSTEM_PLAN', sid => 'PROD' );

exec dbms_resource_manager.clear_pending_area;
exec dbms_resource_manager.create_pending_area;

exec dbms_resource_manager_privs.revoke_switch_consumer_group ('OE', 'SYS_GROUP');
exec dbms_resource_manager_privs.revoke_switch_consumer_group ('OE', 'LOW_GROUP');
exec dbms_resource_manager_privs.revoke_switch_consumer_group ('OE', 'APPUSER');

exec dbms_resource_manager.set_consumer_group_mapping('CLIENT_OS_USER','oracle');
exec dbms_resource_manager.set_consumer_group_mapping('ORACLE_USER','SH');
exec dbms_resource_manager.set_consumer_group_mapping('ORACLE_USER','HR');

exec dbms_resource_manager.delete_plan_directive('prod_plan','APPUSER');
exec dbms_resource_manager.delete_consumer_group('APPUSER');

exec dbms_resource_manager.delete_plan_directive('prod_plan','LOW_GROUP');
exec dbms_resource_manager.delete_plan_directive('prod_plan','OTHER_GROUPS');
exec dbms_resource_manager.delete_plan_directive('prod_plan','SYS_GROUP');

exec dbms_resource_manager.delete_plan('prod_plan');

exec dbms_resource_manager.submit_pending_area();
--------------- 反向操作 结束--------------