-- +----------------------------------------------------------------------------+ -- | Jeffrey M. Hunter | -- | jhunter@idevelopment.info | -- | www.idevelopment.info | -- |----------------------------------------------------------------------------| -- | Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved. | -- |----------------------------------------------------------------------------| -- | DATABASE : Oracle | -- | FILE : example_database_resource_manager_setup.sql | -- | CLASS : Examples | -- | PURPOSE : Example SQL syntax used setup and configure database resource | -- | manager (DRM). | -- | NOTE : As with any code, ensure to test this script in a development | -- | environment before attempting to run it in production. | -- +----------------------------------------------------------------------------+ connect system/manager set serveroutput on Prompt ==================== Prompt Clean-Up DRM Area... Prompt ==================== Prompt accept a1 Prompt "Hit to continue"; alter system set resource_manager_plan=system_plan; exec DBMS_RESOURCE_MANAGER.create_pending_area; -- exec dbms_resource_manager.DELETE_PLAN_CASCADE('daytime_plan'); -- exec dbms_resource_manager.DELETE_PLAN_CASCADE('night_weekend_plan'); exec dbms_resource_manager.DELETE_PLAN_DIRECTIVE('daytime_plan', 'oltp_group'); exec dbms_resource_manager.DELETE_PLAN_DIRECTIVE('daytime_plan', 'batch_group'); exec dbms_resource_manager.DELETE_PLAN_DIRECTIVE('daytime_plan', 'other_groups'); exec dbms_resource_manager.DELETE_PLAN_DIRECTIVE('night_weekend_plan', 'oltp_group'); exec dbms_resource_manager.DELETE_PLAN_DIRECTIVE('night_weekend_plan', 'batch_group'); exec dbms_resource_manager.DELETE_PLAN_DIRECTIVE('night_weekend_plan', 'other_groups'); exec dbms_resource_manager.DELETE_PLAN('daytime_plan'); exec dbms_resource_manager.DELETE_PLAN('night_weekend_plan'); exec dbms_resource_manager.DELETE_CONSUMER_GROUP('oltp_group'); exec dbms_resource_manager.DELETE_CONSUMER_GROUP('batch_group'); exec DBMS_RESOURCE_MANAGER.validate_pending_area; exec DBMS_RESOURCE_MANAGER.submit_pending_area; Prompt ======================== Prompt Creating Pending Area... Prompt ======================== Prompt accept a1 Prompt "Hit to continue"; exec DBMS_RESOURCE_MANAGER.create_pending_area; Prompt ================================ Prompt Create Resource Plan Template... Prompt ================================ Prompt accept a1 Prompt "Hit to continue"; exec DBMS_RESOURCE_MANAGER.create_plan('daytime_plan', 'Plan for daytime processing'); exec DBMS_RESOURCE_MANAGER.create_plan('night_weekend_plan', 'Plan for nights and weekends'); Prompt ========================================== Prompt Create Resource Consumer Group Template... Prompt ========================================== Prompt accept a1 Prompt "Hit to continue"; exec DBMS_RESOURCE_MANAGER.create_consumer_group('oltp_group', 'data entry specialist'); exec DBMS_RESOURCE_MANAGER.create_consumer_group('batch_group', 'nightly batch jobs'); Prompt ================================== Prompt Create Resource Plan Directives... Prompt ================================== Prompt accept a1 Prompt "Hit to continue"; exec DBMS_RESOURCE_MANAGER.create_plan_directive('daytime_plan', 'oltp_group', 'Daytime rule for oltp_group users', cpu_p1 => 90, parallel_degree_limit_p1 => 0); exec DBMS_RESOURCE_MANAGER.create_plan_directive('daytime_plan', 'batch_group', 'Daytime rule for batch_group users', cpu_p1 => 10, parallel_degree_limit_p1 => 0); exec DBMS_RESOURCE_MANAGER.create_plan_directive('daytime_plan', 'other_groups', 'Daytime rules for all other users/groups', cpu_p2 => 100, parallel_degree_limit_p1 => 0); exec DBMS_RESOURCE_MANAGER.create_plan_directive('night_weekend_plan', 'oltp_group', 'Night/Weekend rule for oltp_group users', cpu_p1 => 10, parallel_degree_limit_p1 => 0); exec DBMS_RESOURCE_MANAGER.create_plan_directive('night_weekend_plan', 'batch_group', 'Night/Weekend rule for batch_group users', cpu_p1 => 90, parallel_degree_limit_p1 => 0); exec DBMS_RESOURCE_MANAGER.create_plan_directive('night_weekend_plan', 'other_groups', 'Night/Weekend rules for all other users/groups', cpu_p2 => 100, parallel_degree_limit_p1 => 0); Prompt ================================== Prompt Confirm (Validate) Pending Area... Prompt ================================== Prompt accept a1 Prompt "Hit to continue"; exec DBMS_RESOURCE_MANAGER.validate_pending_area; Prompt ====================== Prompt Submit Pending Area... Prompt ====================== Prompt accept a1 Prompt "Hit to continue"; exec DBMS_RESOURCE_MANAGER.submit_pending_area; -- exec DBMS_RESOURCE_MANAGER.clear_pending_area; Prompt ========================================================================= Prompt ALLOW USER SCOTT TO SWITCH BETWEEN (oltp_group and batch_group) GROUPS... Prompt ========================================================================= Prompt accept a1 Prompt "Hit to continue"; exec DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group('scott', 'oltp_group', FALSE); exec DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group('scott', 'batch_group', FALSE); Prompt ========================================================= Prompt ASSIGN USER SCOTT TO INITIAL CONSUMER GROUP OF oltp_group... Prompt ========================================================= Prompt accept a1 Prompt "Hit to continue"; exec DBMS_RESOURCE_MANAGER.set_initial_consumer_group('scott', 'oltp_group'); Prompt ============================================== Prompt SWITCH USER SCOTT INTO THE oltp_group GROUP... Prompt ============================================== Prompt accept a1 Prompt "Hit to continue"; exec DBMS_RESOURCE_MANAGER.switch_consumer_group_for_user('scott', 'oltp_group'); Prompt ============================== Prompt Switch to DAYTIME_PLAN plan... Prompt ============================== Prompt accept a1 Prompt "Hit to continue"; ALTER SYSTEM SET resource_manager_plan=daytime_plan;