SQL Script Drop Database Resource Manager Plan

Updated : 30-July-2002
Version : 1.0

Description

This script drops a resource manager plan and any associated subplans.

Parameters

&1 - The Plan Name to drop

SQL Source

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

Return to Index of SQL Scripts

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz Legal