/*
|| Database Resource Management Listing 2
||
|| Contains examples of:
|| - Using the Pending Area for staging changes
|| - Clearing existing resource plans, directives, and consumer groups
|| - Creating new resource plans, directives, and consumer groups
|| - Assigning users to default resource consumer groups
|| - Switching users and sessions to permitted resource consumer groups
|| for demonstration of Oracle Database Resource Management (DRM) features.
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of
|| Oracle Database Resource Management (DRM) and should be carefully proofread
|| before executing it against any existing Oracle database to insure
|| that no potential damage can occur.
||
*/

CONNECT SYS / AS SYSDBA;
 
-----
-- Listing 2.1: The Pending Area
-----
-- Create a pending area
EXEC DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;

-- Validate resource plans in the pending area
EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;

-- Submit changes to resource plans stored in the pending area
EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;

-- Clear the pending area
EXEC DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;

----- 
-- Listing 2.2: Remove existing resource plan, resource plan
--              directives, and resource consumer groups
-----
BEGIN
    -- Prepare the pending area
    DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;
    DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;

    -- Delete resource plan, its resource plan directives, and
    -- any associated resource groups
    DBMS_RESOURCE_MANAGER.DELETE_PLAN_CASCADE(
       plan => 'PEAKTIME'
    );

    -- Submit the changes
    DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
    DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;

END;
/

----- 
-- Listing 2.3: Create two complementary basic resource plans
--              (without SIMPLE_PLAN)
-----
BEGIN
    -- Prepare the pending area
    DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;
    DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;

    -- Create resource plans
    DBMS_RESOURCE_MANAGER.CREATE_PLAN(
        plan => 'PEAKTIME'
       ,comment => 'Peak Time'
    );
    DBMS_RESOURCE_MANAGER.CREATE_PLAN(
        plan => 'OFF-PEAK'
       ,comment => 'Off-Peak'
    );

    -- Create resource consumer groups
    DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
        consumer_group => 'OLTP'
       ,comment => 'OnLine Transaction Processing'
       ,cpu_mth => 'ROUND-ROBIN'
    );
    DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
        consumer_group => 'DSS'
       ,comment => 'Decision Support Systems'
       ,cpu_mth => 'ROUND-ROBIN'
    );
    DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
        consumer_group => 'ADHOC'
       ,comment => 'Ad Hoc Query Writers'
       ,cpu_mth => 'ROUND-ROBIN'
    );

    -- Create resource plan directives
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
         plan => 'PEAKTIME'
        ,group_or_subplan => 'OLTP'
        ,comment => 'Peak Time - OLTP'
        ,cpu_p1 => 70
   );
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
         plan => 'PEAKTIME'
        ,group_or_subplan => 'DSS'
        ,comment => 'Peak Time - DSS'
       ,cpu_p1 => 20
    );
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
        plan => 'PEAKTIME'
       ,group_or_subplan => 'ADHOC'
       ,comment => 'Peak Time - Ad Hoc'
       ,cpu_p1 => 10
    );
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
        plan => 'PEAKTIME'
       ,group_or_subplan => 'OTHER_GROUPS'
       ,comment => 'Peak Time - Other Groups (REQUIRED)'
       ,cpu_p1 => 0
       ,cpu_p2 => 100
    );
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
        plan => 'OFF-PEAK'
       ,group_or_subplan => 'OLTP'
       ,comment => 'Off-Peak - OLTP'
       ,cpu_p1 => 20
    );
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
        plan => 'OFF-PEAK'
       ,group_or_subplan => 'DSS'
       ,comment => 'Off-Peak - DSS'
       ,cpu_p1 => 60
    );
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
        plan => 'OFF-PEAK'
       ,group_or_subplan => 'ADHOC'
       ,comment => 'Off-Peak - Ad Hoc'
       ,cpu_p1 => 20
    );
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
        plan => 'OFF-PEAK'
       ,group_or_subplan => 'OTHER_GROUPS'
       ,comment => 'Off-Peak - Other Groups (REQUIRED)'
       ,cpu_p1 => 0
       ,cpu_p2 => 100
    );

    -- Submit the changes
    DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
    DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;

END;
/

----- 
-- Listing 2.4: Maintain relationships between users and resource plans
-----
BEGIN
    -- Prepare the pending area
    DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;
    DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;

    -- Grant each user rights to switch its resource consumer group
    -- (required before setting the initial resource consumer group).
	DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
        grantee_name => 'HR'
       ,consumer_group => 'OLTP'
       ,grant_option => FALSE
    );
	DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
        grantee_name => 'HR'
       ,consumer_group => 'DSS'
       ,grant_option => FALSE
    );
	DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
        grantee_name => 'HR'
       ,consumer_group => 'ADHOC'
       ,grant_option => FALSE
    );
	DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
        grantee_name => 'SLSMGR'
       ,consumer_group => 'OLTP'
       ,grant_option => FALSE
    );
	DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
        grantee_name => 'SLSMGR'
       ,consumer_group => 'DSS'
       ,grant_option => FALSE
    );
	DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
        grantee_name => 'SLSMGR'
       ,consumer_group => 'ADHOC'
       ,grant_option => FALSE
    );

    -- Set users' initial resource groups
	DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP(
        user => 'HR'
       ,consumer_group => 'OLTP'
    );
	DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP(
        user => 'SLSMGR'
       ,consumer_group => 'DSS'
    );

    -- Submit the changes
    DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
    DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;

END;
/

----- 
-- Listing 2.5: Switch a user's consumer group for
--              (a) just one user's session; or
--              (b) all user's sessions
-----
BEGIN
    -- Switch the consumer group for a specific session
    -- of the HR user (found by querying V$SESSION)
    DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS(
        session_id => '17'
       ,session_serial => '11'
       ,consumer_group => 'DSS'
    );

-- Switch the consumer group for all sessions belonging to 
-- the specified users
    DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER(
        USER => 'SLSMGR'
       ,consumer_group => 'DSS'
    );
    DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER(
        USER => 'HR'
       ,consumer_group => 'OLTP'
    );
END;
/