oracle resource management scripts

Create resource manager plan

begin
 
  begin
     dbms_resource_manager.create_simple_plan
        (simple_plan => 'MY_PLAN'
        ,consumer_group1 => 'USERS', group1_cpu => 90
        ,consumer_group2 => 'ADMIN', group2_cpu => 10 );
     exception when others then
      null;
  end;

end;
/

List the configuration of the Database Resource Manager 

set linesize 1000 

col plan form a40 heading "Plan Name"
col group_or_subplan form a15 heading "Sub-Plan"
col status form a6 heading "Status"
col cpu_p1 form 999 heading "CPU1"
col cpu_p2 form 999 heading "CPU2"
col cpu_p3 form 999 heading "CPU3"
col cpu_p4 form 999 heading "CPU4"

PROMPT
PROMPT Plans on database

select plan, cpu_method, status from dba_rsrc_plans; 

PROMPT
PROMPT Resource Plan Directives

select plan, group_or_subplan, type, cpu_p1, cpu_p2, cpu_p3, cpu_p4, status 
  from dba_rsrc_plan_directives 
order by 8,1,2,3,4,5,6; 

PROMPT
PROMPT Consumer Group Privileges

select * from dba_rsrc_consumer_group_privs; 

PROMPT
PROMPT Default User Consumer Groups

select username, initial_rsrc_consumer_group from dba_users; 

assign a resource manager plan to users

Parameters

&1 - The resource consumer group
&2 - The user to be assigned the consumer group

set verify off

define consumer_group = UPPER('&1')
define user = UPPER('&2')

begin
 
dbms_resource_manager_privs.grant_switch_consumer_group  
      ( grantee_name => &user
      , consumer_group => &consumer_group
      , grant_option => FALSE);

dbms_resource_manager.set_initial_consumer_group
     (user => &user
     ,consumer_group  => &consumer_group ); 

end;
/

exit

Monitor Database Resource Manager Sessions

col sid form 999 heading "SID"
col serial# form 99999 heading "Serial"
col program form a28 heading "Program"
col username form a12 heading "Username"
col resource_consumer_group form a22 heading "Consumer Group"
col name form a12 heading "Name"
col active_sessions form 99999 head "Active"
col consumed_cpu_time form 99999999 heading "Con CPU"
col sessions_queued form 99999 heading "Queued" 

spool monrsrc.lst

select sid,serial#,username,program,resource_consumer_group 
from v$session
/

select name,active_sessions,consumed_cpu_time
       ,requests,cpu_wait_time,cpu_waits, sessions_queued
from v$rsrc_consumer_group
/

spool off

Drop a resource manager plan and any associated subplans

Parameters

&1 - The Plan Name to drop

set serveroutput on

col plan form a12 heading "Plan Name"

select plan, cpu_method, status from dba_rsrc_plans; 

ACCEPT plan_name PROMPT 'Enter value for Plan to Drop : '

spool drop_rsrc.lst

DECLARE

l_plan_name DBA_RSRC_PLANS.plan%TYPE := UPPER('&plan_name') ;

BEGIN

   dbms_output.put ('Creating Pending Area...');
   dbms_resource_manager.create_pending_area(); 
   dbms_output.put_line ('Pending Area Created.');

   dbms_output.put ('Deleting Plan '||l_plan_name||'...');
   dbms_resource_manager.delete_plan( plan => l_plan_name); 
   dbms_output.put_line ('Plan Deleted.');

   FOR con_grp IN ( SELECT group_or_subplan 
                    FROM  dba_rsrc_plan_directives 
                    WHERE plan = l_plan_name 
                     ) LOOP

      dbms_output.put ('Deleting Consumer Group '
               ||con_grp.group_or_subplan||'...');
      dbms_resource_manager.delete_consumer_group( 
            consumer_group => con_grp.group_or_subplan ); 
      dbms_output.put_line ('Consumer Group Deleted.');

   END LOOP;

   dbms_output.put ('Submitting Pending Area ...');
   dbms_resource_manager.submit_pending_area(); 
    dbms_output.put_line ('Pending Area Submitted.');

EXCEPTION WHEN others  THEN
   
   dbms_output.new_line ;
   dbms_output.put_line ('Error Occurred  :'||SQLERRM);
   
   dbms_resource_manager.clear_pending_area();

   dbms_output.put_line ('... Pending Area Cleared.');

END;
/

spool off

Lists usage information of consumer groups

SELECT name,consumed_cpu_time
FROM   v$rsrc_consumer_group
ORDER BY name;

Lists all resource plans

SET LINESIZE 200
SET VERIFY OFF

COLUMN status FORMAT A10
COLUMN comments FORMAT A50

SELECT plan,
       status,
       comments
FROM   dba_rsrc_plans
ORDER BY plan;

Lists all consumer groups

SET LINESIZE 200
SET VERIFY OFF

COLUMN status FORMAT A10
COLUMN comments FORMAT A50

SELECT consumer_group,
       status,
       comments
FROM   dba_rsrc_consumer_groups
ORDER BY consumer_group;