只希望限制用户并行度，而不限制用户的CPU等

begin
sys.dbms_resource_manager.clear_pending_area();
sys.dbms_resource_manager.create_pending_area();
sys.dbms_resource_manager.delete_plan_cascade ('ACCT_PLAN');
sys.dbms_resource_manager.submit_pending_area();
end;
/

begin
sys.dbms_resource_manager.clear_pending_area();
sys.dbms_resource_manager.create_pending_area();
sys.dbms_resource_manager.create_plan (
      plan                         => 'ACCT_PLAN'
     ,comment                      => 'ACCT aaplication''s PLAN');
sys.dbms_resource_manager.create_plan_directive (
      plan                         => 'ACCT_PLAN'
     ,group_or_subplan             => 'OTHER_GROUPS'
     ,switch_estimate              => FALSE
     ,comment                      => 'ddd' );
sys.dbms_resource_manager.create_plan_directive (
      plan                         => 'ACCT_PLAN'
     ,group_or_subplan             => 'SYS_GROUP'
     ,switch_estimate              => FALSE
     ,comment                      => 'ccc' );
sys.dbms_resource_manager.create_consumer_group (
      consumer_group               => 'ACCT_GROUP'
     ,comment                      => 'ACCT applications');
sys.dbms_resource_manager.create_plan_directive (
      plan                         => 'ACCT_PLAN'
     ,group_or_subplan             => 'ACCT_GROUP' -- 这一组不是必须的
     ,switch_estimate              => FALSE
     ,comment                      => 'bbb' );
sys.dbms_resource_manager.create_consumer_group (
      consumer_group               => 'CITY_GROUP'
     ,comment                      => 'CITY applications');
sys.dbms_resource_manager.create_plan_directive (
      plan                         => 'ACCT_PLAN'
     ,group_or_subplan             => 'CITY_GROUP'
     ,switch_estimate              => FALSE
     ,parallel_degree_limit_p1     => 2             -- 只指定该参数限制并行
     ,comment                      => 'aaa' );
sys.dbms_resource_manager.submit_pending_area();
end;
/

-- 如下动作必须，否则用户登录后无法切换到指定的资源组
BEGIN
sys.dbms_resource_manager.clear_pending_area();
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(GRANTEE_NAME   => 'PUBLIC',
                            CONSUMER_GROUP => 'ACCT_GROUP',
                            GRANT_OPTION   => TRUE);
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(GRANTEE_NAME   => 'PUBLIC',
                            CONSUMER_GROUP => 'CITY_GROUP',
                            GRANT_OPTION   => TRUE);
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

-- 建立MAPPING
BEGIN
dbms_resource_manager.clear_pending_area();
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(DBMS_RESOURCE_MANAGER.oracle_user,'PZH_YUANJIAMING',   'CITY_GROUP');
dbms_resource_manager.submit_pending_area();
END;
/

alter system set resource_manager_plan=ACCT_PLAN;
如果是11g，注意把autotask的windows的resource_manager_plan给关闭掉

ITPub博客

使用resource_manager限制用户并行度

最新文章