programming/divers/oracle_ressource_manager_01.txt

https://oracle-base.com/articles/8i/resource-manager-8i


orapwd file=orapwHUTTPRD password="Urezesf7754#hhY7711#ab?"


create pluggable database DURGA admin user HUTTMASTER identified by "Ngfsf554#hhAZAR1#10!";
alter pluggable database DURGA open;
alter pluggable database DURGA save state;

alter session set container=DURGA;
show con_name
grant sysdba to starkiller identified by "VvvAv0332#00911HsqeZA?";



alias HUTTPRD='rlwrap sqlplus sys/"Urezesf7754#hhY7711#ab?"@taris/HUTTPRD as sysdba'
alias DURGA='rlwrap sqlplus starkiller/"VvvAv0332#00911HsqeZA?"@taris/DURGA as sysdba'


CREATE USER web_user identified by "iN_j8sC#d!kX6b:_";
CREATE USER batch_user identified by "r~65ktuFYyds+P_X";

grant connect,resource to web_user;
grant connect,resource to batch_user;

# create a pending area.

BEGIN
  DBMS_RESOURCE_MANAGER.clear_pending_area;
  DBMS_RESOURCE_MANAGER.create_pending_area;
END;
/

# create a plan

BEGIN
  DBMS_RESOURCE_MANAGER.create_plan(
    plan    => 'hybrid_plan',
    comment => 'Plan for a combination of high and low priority tasks.');
END;
/

Create a web and a batch consumer group

BEGIN
  DBMS_RESOURCE_MANAGER.create_consumer_group(
    consumer_group => 'WEB_CG',
    comment        => 'Web based OTLP processing - high priority');

  DBMS_RESOURCE_MANAGER.create_consumer_group(
    consumer_group => 'BATCH_CG',
    comment        => 'Batch processing - low priority');
END;
/

# assign the consumer groups to the plan and indicate their relative priority, remembering to add the OTHER_GROUPS plan directive.


BEGIN
  DBMS_RESOURCE_MANAGER.create_plan_directive (
    plan                     => 'hybrid_plan',
    group_or_subplan         => 'web_cg',
    comment                  => 'High Priority',
    cpu_p1                   => 80,
    cpu_p2                   => 0,
    parallel_degree_limit_p1 => 4);

  DBMS_RESOURCE_MANAGER.create_plan_directive (
    plan                     => 'hybrid_plan',
    group_or_subplan         => 'batch_cg',
    comment                  => 'Low Priority',
    cpu_p1                   => 0,
    cpu_p2                   => 80,
    parallel_degree_limit_p1 => 4);

  DBMS_RESOURCE_MANAGER.create_plan_directive(
    plan             => 'hybrid_plan',
    group_or_subplan => 'OTHER_GROUPS',
    comment          => 'all other users - level 3',
    cpu_p1           => 0,
    cpu_p2           => 0,
    cpu_p3           => 100);
END;
/

# validate and apply the resource plan.

BEGIN
  DBMS_RESOURCE_MANAGER.validate_pending_area;
  DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/


#  assign our users to individual consumer groups.

BEGIN
  -- Assign users to consumer groups
  DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
    grantee_name   => 'web_user',
    consumer_group => 'web_cg',
    grant_option   => FALSE);

  DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
    grantee_name   => 'batch_user',
    consumer_group => 'batch_cg',
    grant_option   => FALSE);

  DBMS_RESOURCE_MANAGER.set_initial_consumer_group('web_user', 'web_cg');

  DBMS_RESOURCE_MANAGER.set_initial_consumer_group('batch_user', 'batch_cg');
END;
/




connect web_user/"iN_j8sC#d!kX6b:_"@taris/DURGA
connect batch_user/"r~65ktuFYyds+P_X"@taris/DURGA

SELECT username, resource_consumer_group 
  FROM   v$session
  WHERE  username IN ('WEB_USER','BATCH_USER');


ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'hybrid_plan';