Dbms_Resource_Manager相关

Begin
  -- 创建Pending_Area
  Dbms_Resource_Manager.Create_Pending_Area();

  -- 创建资源计划DAYTIME
  Dbms_Resource_Manager.Create_Plan(Plan    => 'DAYTIME',
                                    Comment => 'More resources for OLTP applications');

  -- 创建资源计划组OLTP,REPORTING,OTHER_GROUPS
  -- 同时设定各资源计划组使用的资源限制
  Dbms_Resource_Manager.Create_Consumer_Group(Consumer_Group => 'OLTP',
                                              Comment        => 'OLTP applications');

  Dbms_Resource_Manager.Create_Consumer_Group(Consumer_Group => 'REPORTING',
                                              Comment        => 'report applications');
                                             
  Dbms_Resource_Manager.Create_Plan_Directive(Plan             => 'DAYTIME',
                                              Group_Or_Subplan => 'OLTP',
                                              Comment          => 'OLTP group',
                                              Mgmt_P1          => 75);

  Dbms_Resource_Manager.Create_Plan_Directive(Plan                     => 'DAYTIME',
                                              Group_Or_Subplan         => 'REPORTING',
                                              Comment                  => 'Reporting group',
                                              Mgmt_P1                  => 15,
                                              Parallel_Degree_Limit_P1 => 8,
                                              Active_Sess_Pool_P1      => 1);

  Dbms_Resource_Manager.Create_Plan_Directive(Plan             => 'DAYTIME',
                                              Group_Or_Subplan => 'OTHER_GROUPS',
                                              Comment          => 'This one is required',
                                              Mgmt_P1          => 10);

  -- 校验Pending_Area
  Dbms_Resource_Manager.Validate_Pending_Area();
  Dbms_Resource_Manager.Submit_Pending_Area();
  Dbms_Resource_Manager.Clear_Pending_Area();
End;

 

-- 用资源计划限制
SQL> alter system set resource_limit=true scope=both;

-- 指定RESOURCE_MANAGER_PLAN
SQL>ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'DAYTIME' scope=both;

-- 允许某个用户切换到资源组
BEGIN
DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
   grantee_name   => 'HXL',
   consumer_group => 'OLTP',
   grant_option   => FALSE);
END;

-- 资源计划组绑定用户
BEGIN
  Dbms_Resource_Manager.Create_Pending_Area();
  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING 
     (DBMS_RESOURCE_MANAGER.ORACLE_USER, 'HXL', 'REPORTING');
  Dbms_Resource_Manager.Validate_Pending_Area();
  Dbms_Resource_Manager.Submit_Pending_Area();
  Dbms_Resource_Manager.Clear_Pending_Area();
END;

-- 用户制定使用 REPORTING 时,update的时候会hang住