Thursday, August 21, 2025

Resource Plan


Adding user to the existing group
+++++++++++++++++++++++++++++++++++

SET SERVEROUTPUT ON;
-- Assumptions:
-- 1. Running on Oracle RDS with Enterprise Edition.
-- 2. User has ADMINISTER_RESOURCE_MANAGER privilege.
-- 3. PLAN1 and LOW_GROUP exist; LOW_GROUP has 30% CPU limit (UTILIZATION_LIMIT => 30).
-- 4. Adds USER_A to LOW_GROUP, sharing 30% CPU limit with existing users (e.g., USER_ABC, USER_123).
-- 5. No changes to PLAN1 directives or other groups (HIGH_GROUP, OTHER_GROUPS).
-- 6. No database bounce required; changes apply dynamically.
DECLARE
  l_username CONSTANT VARCHAR2(128) := 'USER_A'; -- User to add
  l_group_exists NUMBER;
  l_user_exists NUMBER;
  l_mapping_exists NUMBER;
  l_error_message VARCHAR2(4000);
BEGIN
  -- Step 1: Check if LOW_GROUP Exists
  DBMS_OUTPUT.PUT_LINE('Step 1: Checking if consumer group LOW_GROUP exists...');
  BEGIN
    SELECT COUNT(*) INTO l_group_exists
    FROM dba_rsrc_consumer_groups
    WHERE consumer_group = 'LOW_GROUP';
    IF l_group_exists = 0 THEN
      RAISE_APPLICATION_ERROR(-20001, 'LOW_GROUP does not exist. Cannot proceed.');
    END IF;
    DBMS_OUTPUT.PUT_LINE('LOW_GROUP exists.');
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 1 (Check LOW_GROUP): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;

  -- Step 2: Check if User Exists
  DBMS_OUTPUT.PUT_LINE('Step 2: Checking if user ' || l_username || ' exists...');
  BEGIN
    SELECT COUNT(*) INTO l_user_exists
    FROM dba_users
    WHERE username = UPPER(l_username);
    IF l_user_exists = 0 THEN
      EXECUTE IMMEDIATE 'CREATE USER ' || DBMS_ASSERT.SQL_OBJECT_NAME(l_username) || ' IDENTIFIED BY secure_password';
      EXECUTE IMMEDIATE 'GRANT CREATE SESSION TO ' || DBMS_ASSERT.SQL_OBJECT_NAME(l_username);
      DBMS_OUTPUT.PUT_LINE('User ' || l_username || ' created with CREATE SESSION privilege.');
    ELSE
      DBMS_OUTPUT.PUT_LINE('User ' || l_username || ' already exists.');
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 2 (Check/Create User): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;

  -- Step 3: Create Pending Area
  DBMS_OUTPUT.PUT_LINE('Step 3: Creating pending area...');
  BEGIN
    DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
    DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
    DBMS_OUTPUT.PUT_LINE('Pending area created.');
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 3 (Create Pending Area): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;

  -- Step 4: Grant Switch Privilege to USER_A
  DBMS_OUTPUT.PUT_LINE('Step 4: Granting switch privilege to ' || l_username || ' for LOW_GROUP...');
  BEGIN
    DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
      GRANTEE_NAME => l_username,
      CONSUMER_GROUP => 'LOW_GROUP',
      GRANT_OPTION => FALSE
    );
    DBMS_OUTPUT.PUT_LINE('Switch privilege granted to ' || l_username || ' for LOW_GROUP.');
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 4 (Grant Switch Privilege): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;

  -- Step 5: Map USER_A to LOW_GROUP
  DBMS_OUTPUT.PUT_LINE('Step 5: Mapping ' || l_username || ' to LOW_GROUP...');
  BEGIN
    SELECT COUNT(*) INTO l_mapping_exists
    FROM dba_rsrc_group_mappings
    WHERE attribute = 'ORACLE_USER' AND value = UPPER(l_username);
    IF l_mapping_exists = 0 THEN
      DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
        ATTRIBUTE => DBMS_RESOURCE_MANAGER.ORACLE_USER,
        VALUE => UPPER(l_username),
        CONSUMER_GROUP => 'LOW_GROUP'
      );
      DBMS_OUTPUT.PUT_LINE('Mapping created for ' || l_username || ' to LOW_GROUP.');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Mapping for ' || l_username || ' already exists.');
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 5 (Map User): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;

  -- Step 6: Validate Pending Area
  DBMS_OUTPUT.PUT_LINE('Step 6: Validating pending area...');
  BEGIN
    DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
    DBMS_OUTPUT.PUT_LINE('Pending area validated successfully.');
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 6 (Validate Pending Area): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;

  -- Step 7: Submit Pending Area
  DBMS_OUTPUT.PUT_LINE('Step 7: Submitting pending area...');
  BEGIN
    DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
    DBMS_OUTPUT.PUT_LINE('Pending area submitted successfully.');
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 7 (Submit Pending Area): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;

  -- Step 8: Verify Configuration
  DBMS_OUTPUT.PUT_LINE('Step 8: Verifying configuration...');
  BEGIN
    SELECT COUNT(*) INTO l_mapping_exists
    FROM dba_rsrc_group_mappings
    WHERE attribute = 'ORACLE_USER' AND value = UPPER(l_username) AND consumer_group = 'LOW_GROUP';
    IF l_mapping_exists > 0 THEN
      DBMS_OUTPUT.PUT_LINE('Verified: ' || l_username || ' mapped to LOW_GROUP.');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Warning: ' || l_username || ' mapping to LOW_GROUP not found.');
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 8 (Verify Configuration): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;

  DBMS_OUTPUT.PUT_LINE('Successfully added ' || l_username || ' to LOW_GROUP.');

EXCEPTION
  WHEN OTHERS THEN
    -- Clear pending area on error to avoid locking
    BEGIN
      DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
      DBMS_OUTPUT.PUT_LINE('Pending area cleared due to error.');
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error clearing pending area: ' || SQLERRM);
    END;
    RAISE_APPLICATION_ERROR(-20001, 'Critical error: ' || l_error_message);
END;
/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Move user from one group to other:

SET SERVEROUTPUT ON;
-- Assumptions:
-- 1. Running on Oracle RDS with Enterprise Edition.
-- 2. User has ADMINISTER_RESOURCE_MANAGER privilege; ALTER SYSTEM is skipped to avoid ORA-01031.
-- 3. PLAN1 exists with RATIO method; LOW_GROUP has 30% UTILIZATION_LIMIT; HIGH_GROUP, OTHER_GROUPS exist.
-- 4. Moves USER_A from LOW_GROUP to HIGH_GROUP, which already exists in PLAN1.
-- 5. No changes to PLAN1 directives, MGMT_P1, or other groups.
-- 6. Uses SET_CONSUMER_GROUP_MAPPING to remove existing mapping, avoiding DELETE_CONSUMER_GROUP_MAPPING error.
-- 7. No database bounce required; changes apply dynamically.
DECLARE
  l_username CONSTANT VARCHAR2(128) := 'USER_A'; -- User to move
  l_target_group CONSTANT VARCHAR2(128) := 'HIGH_GROUP'; -- Target group
  l_group_exists NUMBER;
  l_user_exists NUMBER;
  l_mapping_exists NUMBER;
  l_current_group VARCHAR2(128);
  l_error_message VARCHAR2(4000);

  -- Function to check if a plan exists (corrected from your earlier code)
  FUNCTION plan_exists(p_plan IN VARCHAR2) RETURN BOOLEAN IS
    l_count NUMBER;
  BEGIN
    SELECT COUNT(*) INTO l_count
    FROM dba_rsrc_plans
    WHERE plan = UPPER(p_plan)
    AND NVL(status, 'ACTIVE') != 'PENDING';
    RETURN l_count > 0;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RETURN FALSE;
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error in plan_exists for ' || p_plan || ': ' || SQLERRM);
      RETURN FALSE;
  END plan_exists;

BEGIN
  -- Step 1: Check if PLAN1 Exists
  DBMS_OUTPUT.PUT_LINE('Step 1: Checking if PLAN1 exists...');
  BEGIN
    IF NOT plan_exists('PLAN1') THEN
      RAISE_APPLICATION_ERROR(-20001, 'PLAN1 does not exist. Cannot proceed.');
    END IF;
    DBMS_OUTPUT.PUT_LINE('PLAN1 exists.');
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 1 (Check PLAN1): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;

  -- Step 2: Check if User Exists
  DBMS_OUTPUT.PUT_LINE('Step 2: Checking if user ' || l_username || ' exists...');
  BEGIN
    SELECT COUNT(*) INTO l_user_exists
    FROM dba_users
    WHERE username = UPPER(l_username);
    IF l_user_exists = 0 THEN
      EXECUTE IMMEDIATE 'CREATE USER ' || DBMS_ASSERT.SQL_OBJECT_NAME(l_username) || ' IDENTIFIED BY secure_password';
      EXECUTE IMMEDIATE 'GRANT CREATE SESSION TO ' || DBMS_ASSERT.SQL_OBJECT_NAME(l_username);
      DBMS_OUTPUT.PUT_LINE('User ' || l_username || ' created with CREATE SESSION privilege.');
    ELSE
      DBMS_OUTPUT.PUT_LINE('User ' || l_username || ' already exists.');
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 2 (Check/Create User): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;

  -- Step 3: Check if HIGH_GROUP Exists
  DBMS_OUTPUT.PUT_LINE('Step 3: Checking if consumer group ' || l_target_group || ' exists...');
  BEGIN
    SELECT COUNT(*) INTO l_group_exists
    FROM dba_rsrc_consumer_groups
    WHERE consumer_group = l_target_group;
    IF l_group_exists = 0 THEN
      RAISE_APPLICATION_ERROR(-20002, 'HIGH_GROUP does not exist. Cannot proceed.');
    END IF;
    DBMS_OUTPUT.PUT_LINE('HIGH_GROUP exists.');
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 3 (Check HIGH_GROUP): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;

  -- Step 4: Check Current Consumer Group Mapping
  DBMS_OUTPUT.PUT_LINE('Step 4: Checking current consumer group for ' || l_username || '...');
  BEGIN
    SELECT COUNT(*), MAX(consumer_group) INTO l_mapping_exists, l_current_group
    FROM dba_rsrc_group_mappings
    WHERE attribute = 'ORACLE_USER' AND value = UPPER(l_username);
    IF l_mapping_exists > 0 THEN
      DBMS_OUTPUT.PUT_LINE(l_username || ' is currently mapped to ' || l_current_group || '.');
    ELSE
      DBMS_OUTPUT.PUT_LINE(l_username || ' is not mapped to any consumer group (defaults to OTHER_GROUPS).');
      l_current_group := NULL;
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 4 (Check Mapping): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;

  -- Step 5: Create Pending Area
  DBMS_OUTPUT.PUT_LINE('Step 5: Creating pending area...');
  BEGIN
    DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
    DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
    DBMS_OUTPUT.PUT_LINE('Pending area created.');
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 5 (Create Pending Area): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;

  -- Step 6: Remove User from Current Consumer Group
  DBMS_OUTPUT.PUT_LINE('Step 6: Removing ' || l_username || ' from current consumer group...');
  BEGIN
    IF l_mapping_exists > 0 THEN
      DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
        ATTRIBUTE => DBMS_RESOURCE_MANAGER.ORACLE_USER,
        VALUE => UPPER(l_username),
        CONSUMER_GROUP => NULL
      );
      DBMS_OUTPUT.PUT_LINE(l_username || ' removed from ' || l_current_group || '.');
    ELSE
      DBMS_OUTPUT.PUT_LINE(l_username || ' was not mapped; no removal needed.');
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 6 (Remove Mapping): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;

  -- Step 7: Grant Switch Privilege to USER_A for HIGH_GROUP
  DBMS_OUTPUT.PUT_LINE('Step 7: Granting switch privilege to ' || l_username || ' for ' || l_target_group || '...');
  BEGIN
    DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
      GRANTEE_NAME => l_username,
      CONSUMER_GROUP => l_target_group,
      GRANT_OPTION => FALSE
    );
    DBMS_OUTPUT.PUT_LINE('Switch privilege granted to ' || l_username || ' for ' || l_target_group || '.');
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 7 (Grant Switch Privilege): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;

  -- Step 8: Map USER_A to HIGH_GROUP
  DBMS_OUTPUT.PUT_LINE('Step 8: Mapping ' || l_username || ' to ' || l_target_group || '...');
  BEGIN
    DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
      ATTRIBUTE => DBMS_RESOURCE_MANAGER.ORACLE_USER,
      VALUE => UPPER(l_username),
      CONSUMER_GROUP => l_target_group
    );
    DBMS_OUTPUT.PUT_LINE('Mapping created/updated for ' || l_username || ' to ' || l_target_group || '.');
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 8 (Map User): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;

  -- Step 9: Validate Pending Area
  DBMS_OUTPUT.PUT_LINE('Step 9: Validating pending area...');
  BEGIN
    DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
    DBMS_OUTPUT.PUT_LINE('Pending area validated successfully.');
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 9 (Validate Pending Area): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;

  -- Step 10: Submit Pending Area
  DBMS_OUTPUT.PUT_LINE('Step 10: Submitting pending area...');
  BEGIN
    DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
    DBMS_OUTPUT.PUT_LINE('Pending area submitted successfully.');
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 10 (Submit Pending Area): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;

  /* Step 11: Ensure PLAN1 is Active (Commented out to avoid ORA-01031)
  DBMS_OUTPUT.PUT_LINE('Step 11: Ensuring PLAN1 is active...');
  BEGIN
    SELECT value INTO l_current_plan
    FROM v$parameter
    WHERE name = 'resource_manager_plan';
    IF l_current_plan != 'PLAN1' THEN
      DBMS_OUTPUT.PUT_LINE('Warning: PLAN1 is not active (current plan: ' || NVL(l_current_plan, 'none') || ').');
      BEGIN
        EXECUTE IMMEDIATE 'ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = ''PLAN1'' SCOPE=BOTH';
        DBMS_OUTPUT.PUT_LINE('PLAN1 set as active resource plan.');
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('Warning: Cannot set PLAN1 due to insufficient privileges: ' || SQLERRM);
          DBMS_OUTPUT.PUT_LINE('Please run as master user: ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = ''PLAN1'' SCOPE=BOTH;');
      END;
    ELSE
      DBMS_OUTPUT.PUT_LINE('PLAN1 is already active.');
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 11 (Check/Set PLAN1): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;
  */

  -- Step 12: Verify Configuration
  DBMS_OUTPUT.PUT_LINE('Step 12: Verifying configuration...');
  BEGIN
    SELECT COUNT(*) INTO l_mapping_exists
    FROM dba_rsrc_group_mappings
    WHERE attribute = 'ORACLE_USER' AND value = UPPER(l_username) AND consumer_group = l_target_group;
    IF l_mapping_exists > 0 THEN
      DBMS_OUTPUT.PUT_LINE('Verified: ' || l_username || ' mapped to ' || l_target_group || '.');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Warning: ' || l_username || ' mapping to ' || l_target_group || ' not found.');
    END IF;
    -- Check active sessions
    FOR s IN (
      SELECT sid, resource_consumer_group
      FROM v$session
      WHERE username = UPPER(l_username)
    ) LOOP
      DBMS_OUTPUT.PUT_LINE('Session SID ' || s.sid || ' is using consumer group: ' || NVL(s.resource_consumer_group, 'NONE'));
    END LOOP;
    SELECT COUNT(*) INTO l_mapping_exists
    FROM v$session
    WHERE username = UPPER(l_username);
    IF l_mapping_exists = 0 THEN
      DBMS_OUTPUT.PUT_LINE('No active sessions found for ' || l_username || '.');
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 12 (Verify Configuration): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;

  DBMS_OUTPUT.PUT_LINE('Successfully moved ' || l_username || ' from ' || NVL(l_current_group, 'no group') || ' to ' || l_target_group || ' under PLAN1.');

EXCEPTION
  WHEN OTHERS THEN
    -- Clear pending area on error to avoid locking
    BEGIN
      DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
      DBMS_OUTPUT.PUT_LINE('Pending area cleared due to error.');
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error clearing pending area: ' || SQLERRM);
    END;
    RAISE_APPLICATION_ERROR(-20001, 'Critical error: ' || l_error_message);
END;
/

++++++++++++++++++
Create a new group
++++++++++++++++++
SET SERVEROUTPUT ON;
-- Assumptions:
-- 1. Running on Oracle RDS with Enterprise Edition.
-- 2. User has ADMINISTER_RESOURCE_MANAGER privilege.
-- 3. Limits USER_A to 70% CPU utilization.
-- 4. Minimal OTHER_GROUPS directive included to satisfy Oracle requirements.
-- 5. No database bounce required; changes apply dynamically.
DECLARE
  l_plan_exists NUMBER;
  l_group_exists NUMBER;
  l_mapping_exists NUMBER;
  l_error_message VARCHAR2(4000);
  l_username CONSTANT VARCHAR2(128) := 'USER_A';
  l_cpu_limit CONSTANT NUMBER := 70; -- CPU limit percentage
BEGIN
  -- Step 0: Create User (If Not Exists)
  DBMS_OUTPUT.PUT_LINE('Step 0: Checking and creating user ' || l_username || ' if not exists...');
  BEGIN
    SELECT COUNT(*) INTO l_plan_exists
    FROM dba_users
    WHERE username = UPPER(l_username);
    IF l_plan_exists = 0 THEN
      EXECUTE IMMEDIATE 'CREATE USER ' || DBMS_ASSERT.SQL_OBJECT_NAME(l_username) || ' IDENTIFIED BY secure_password';
      EXECUTE IMMEDIATE 'GRANT CREATE SESSION TO ' || DBMS_ASSERT.SQL_OBJECT_NAME(l_username);
      DBMS_OUTPUT.PUT_LINE('User ' || l_username || ' created with CREATE SESSION privilege.');
    ELSE
      DBMS_OUTPUT.PUT_LINE('User ' || l_username || ' already exists.');
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 0 (Create User): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;

  -- Step 1: Create Pending Area
  DBMS_OUTPUT.PUT_LINE('Step 1: Creating pending area...');
  BEGIN
    DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
    DBMS_OUTPUT.PUT_LINE('Pending area created.');
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 1 (Create Pending Area): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;

  -- Step 2: Create Consumer Group (If Not Exists)
  DBMS_OUTPUT.PUT_LINE('Step 2: Checking and creating consumer group LIMITED_USER_GROUP...');
  BEGIN
    SELECT COUNT(*) INTO l_group_exists
    FROM dba_rsrc_consumer_groups
    WHERE consumer_group = 'LIMITED_USER_GROUP';
    IF l_group_exists = 0 THEN
      DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
        CONSUMER_GROUP => 'LIMITED_USER_GROUP',
        COMMENT => 'Group for user ' || l_username || ' with CPU limit'
      );
      DBMS_OUTPUT.PUT_LINE('Consumer group LIMITED_USER_GROUP created.');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Consumer group LIMITED_USER_GROUP already exists.');
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 2 (Create Consumer Group): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;

  -- Step 3: Create Resource Plan (If Not Exists)
  DBMS_OUTPUT.PUT_LINE('Step 3: Checking and creating resource plan CPU_LIMIT_PLAN...');
  BEGIN
    SELECT COUNT(*) INTO l_plan_exists
    FROM dba_rsrc_plans
    WHERE plan = 'CPU_LIMIT_PLAN';
    IF l_plan_exists = 0 THEN
      DBMS_RESOURCE_MANAGER.CREATE_PLAN(
        PLAN => 'CPU_LIMIT_PLAN',
        COMMENT => 'Plan to limit CPU for user ' || l_username
      );
      DBMS_OUTPUT.PUT_LINE('Resource plan CPU_LIMIT_PLAN created.');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Resource plan CPU_LIMIT_PLAN already exists.');
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 3 (Create Resource Plan): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;

  -- Step 4: Create Plan Directives
  DBMS_OUTPUT.PUT_LINE('Step 4: Creating or updating plan directives...');
  BEGIN
    -- Directive for LIMITED_USER_GROUP
    SELECT COUNT(*) INTO l_plan_exists
    FROM dba_rsrc_plan_directives
    WHERE plan = 'CPU_LIMIT_PLAN' AND group_or_subplan = 'LIMITED_USER_GROUP';
    IF l_plan_exists = 0 THEN
      DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
        PLAN => 'CPU_LIMIT_PLAN',
        GROUP_OR_SUBPLAN => 'LIMITED_USER_GROUP',
        COMMENT => 'Limit CPU to ' || l_cpu_limit || '% for ' || l_username,
        MGMT_P1 => l_cpu_limit,  -- Proportional share
        UTILIZATION_LIMIT => l_cpu_limit  -- Absolute CPU cap
      );
      DBMS_OUTPUT.PUT_LINE('Directive for LIMITED_USER_GROUP created.');
    ELSE
      DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
        PLAN => 'CPU_LIMIT_PLAN',
        GROUP_OR_SUBPLAN => 'LIMITED_USER_GROUP',
        NEW_COMMENT => 'Limit CPU to ' || l_cpu_limit || '% for ' || l_username,
        NEW_MGMT_P1 => l_cpu_limit,
        NEW_UTILIZATION_LIMIT => l_cpu_limit
      );
      DBMS_OUTPUT.PUT_LINE('Directive for LIMITED_USER_GROUP updated.');
    END IF;

    -- Minimal directive for OTHER_GROUPS (required to avoid ORA-29363)
    SELECT COUNT(*) INTO l_plan_exists
    FROM dba_rsrc_plan_directives
    WHERE plan = 'CPU_LIMIT_PLAN' AND group_or_subplan = 'OTHER_GROUPS';
    IF l_plan_exists = 0 THEN
      DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
        PLAN => 'CPU_LIMIT_PLAN',
        GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
        COMMENT => 'Minimal directive for other groups',
        MGMT_P1 => 100 - l_cpu_limit  -- Remaining CPU share
      );
      DBMS_OUTPUT.PUT_LINE('Minimal directive for OTHER_GROUPS created.');
    ELSE
      DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
        PLAN => 'CPU_LIMIT_PLAN',
        GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
        NEW_COMMENT => 'Minimal directive for other groups',
        NEW_MGMT_P1 => 100 - l_cpu_limit
      );
      DBMS_OUTPUT.PUT_LINE('Minimal directive for OTHER_GROUPS updated.');
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 4 (Create Plan Directives): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;

  -- Step 5: Grant Switch Privileges to USER_A
  DBMS_OUTPUT.PUT_LINE('Step 5: Granting switch privileges to ' || l_username || '...');
  BEGIN
    DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
      GRANTEE_NAME => l_username,
      CONSUMER_GROUP => 'LIMITED_USER_GROUP',
      GRANT_OPTION => FALSE
    );
    DBMS_OUTPUT.PUT_LINE('Switch privilege granted to ' || l_username || ' for LIMITED_USER_GROUP.');
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 5 (Grant Switch Privileges): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;

  -- Step 6: Map USER_A to LIMITED_USER_GROUP
  DBMS_OUTPUT.PUT_LINE('Step 6: Mapping ' || l_username || ' to LIMITED_USER_GROUP...');
  BEGIN
    SELECT COUNT(*) INTO l_mapping_exists
    FROM dba_rsrc_group_mappings
    WHERE attribute = 'ORACLE_USER' AND value = UPPER(l_username);
    IF l_mapping_exists = 0 THEN
      DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
        ATTRIBUTE => DBMS_RESOURCE_MANAGER.ORACLE_USER,
        VALUE => UPPER(l_username),
        CONSUMER_GROUP => 'LIMITED_USER_GROUP'
      );
      DBMS_OUTPUT.PUT_LINE('Mapping created for ' || l_username || ' to LIMITED_USER_GROUP.');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Mapping for ' || l_username || ' already exists.');
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 6 (Map User): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;

  -- Step 7: Validate Pending Area
  DBMS_OUTPUT.PUT_LINE('Step 7: Validating pending area...');
  BEGIN
    DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
    DBMS_OUTPUT.PUT_LINE('Pending area validated successfully.');
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 7 (Validate Pending Area): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;

  -- Step 8: Submit Pending Area
  DBMS_OUTPUT.PUT_LINE('Step 8: Submitting pending area...');
  BEGIN
    DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
    DBMS_OUTPUT.PUT_LINE('Pending area submitted successfully.');
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 8 (Submit Pending Area): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;

  -- Step 9: Enable Resource Plan
  DBMS_OUTPUT.PUT_LINE('Step 9: Enabling resource plan CPU_LIMIT_PLAN...');
  BEGIN
    EXECUTE IMMEDIATE 'ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = ''CPU_LIMIT_PLAN'' SCOPE=BOTH';
    DBMS_OUTPUT.PUT_LINE('Resource plan CPU_LIMIT_PLAN enabled.');
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 9 (Enable Resource Plan): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;

  -- Step 10: Verify Configuration
  DBMS_OUTPUT.PUT_LINE('Step 10: Verifying configuration...');
  BEGIN
    SELECT value INTO l_error_message
    FROM v$parameter
    WHERE name = 'resource_manager_plan';
    IF l_error_message = 'CPU_LIMIT_PLAN' THEN
      DBMS_OUTPUT.PUT_LINE('Verified: Resource plan CPU_LIMIT_PLAN is active.');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Warning: Resource plan is ' || NVL(l_error_message, 'not set') || ', expected CPU_LIMIT_PLAN.');
    END IF;

    SELECT COUNT(*) INTO l_mapping_exists
    FROM dba_rsrc_group_mappings
    WHERE attribute = 'ORACLE_USER' AND value = UPPER(l_username) AND consumer_group = 'LIMITED_USER_GROUP';
    IF l_mapping_exists > 0 THEN
      DBMS_OUTPUT.PUT_LINE('Verified: ' || l_username || ' mapped to LIMITED_USER_GROUP.');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Warning: ' || l_username || ' mapping to LIMITED_USER_GROUP not found.');
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 10 (Verify Configuration): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;

  DBMS_OUTPUT.PUT_LINE('Resource Manager setup for ' || l_username || ' completed successfully.');

EXCEPTION
  WHEN OTHERS THEN
    -- Clear pending area on error to avoid locking
    BEGIN
      DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
      DBMS_OUTPUT.PUT_LINE('Pending area cleared due to error.');
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error clearing pending area: ' || SQLERRM);
    END;
    RAISE_APPLICATION_ERROR(-20001, 'Critical error: ' || l_error_message);
END;
/

+++

to the existing one

SET SERVEROUTPUT ON;
-- Assumptions:
-- 1. Running on Oracle RDS with Enterprise Edition.
-- 2. User has ADMINISTER_RESOURCE_MANAGER privilege.
-- 3. PLAN1 exists with RATIO method; LOW_GROUP has 30% UTILIZATION_LIMIT.
-- 4. Adds USER_A to LOW_GROUP, sharing 30% CPU limit with USER_ABC, USER_123.
-- 5. No changes to PLAN1 directives, MGMT_P1, or other groups (HIGH_GROUP, OTHER_GROUPS).
-- 6. No database bounce required; changes apply dynamically.
DECLARE
l_username CONSTANT VARCHAR2(128) := 'USER_A'; -- User to add
l_group_exists NUMBER;
l_user_exists NUMBER;
l_mapping_exists NUMBER;
l_error_message VARCHAR2(4000);

-- Function to check if a plan exists (from your code, corrected)
FUNCTION plan_exists(p_plan IN VARCHAR2) RETURN BOOLEAN IS
l_count NUMBER;
BEGIN
SELECT COUNT(*) INTO l_count
FROM dba_rsrc_plans
WHERE plan = UPPER(p_plan)
AND NVL(status, 'ACTIVE') != 'PENDING';
RETURN l_count > 0;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN FALSE;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error in plan_exists for ' || p_plan || ': ' || SQLERRM);
RETURN FALSE;
END plan_exists;

BEGIN
-- Step 1: Check if PLAN1 Exists
DBMS_OUTPUT.PUT_LINE('Step 1: Checking if PLAN1 exists...');
BEGIN
IF NOT plan_exists('PLAN1') THEN
RAISE_APPLICATION_ERROR(-20001, 'PLAN1 does not exist. Cannot proceed.');
END IF;
DBMS_OUTPUT.PUT_LINE('PLAN1 exists.');
EXCEPTION
WHEN OTHERS THEN
l_error_message := 'Error in Step 1 (Check PLAN1): ' || SQLERRM;
DBMS_OUTPUT.PUT_LINE(l_error_message);
RAISE;
END;

-- Step 2: Check if LOW_GROUP Exists
DBMS_OUTPUT.PUT_LINE('Step 2: Checking if consumer group LOW_GROUP exists...');
BEGIN
SELECT COUNT(*) INTO l_group_exists
FROM dba_rsrc_consumer_groups
WHERE consumer_group = 'LOW_GROUP';
IF l_group_exists = 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'LOW_GROUP does not exist. Cannot proceed.');
END IF;
DBMS_OUTPUT.PUT_LINE('LOW_GROUP exists.');
EXCEPTION
WHEN OTHERS THEN
l_error_message := 'Error in Step 2 (Check LOW_GROUP): ' || SQLERRM;
DBMS_OUTPUT.PUT_LINE(l_error_message);
RAISE;
END;

-- Step 3: Check if User Exists
DBMS_OUTPUT.PUT_LINE('Step 3: Checking if user ' || l_username || ' exists...');
BEGIN
SELECT COUNT(*) INTO l_user_exists
FROM dba_users
WHERE username = UPPER(l_username);
IF l_user_exists = 0 THEN
EXECUTE IMMEDIATE 'CREATE USER ' || DBMS_ASSERT.SQL_OBJECT_NAME(l_username) || ' IDENTIFIED BY secure_password';
EXECUTE IMMEDIATE 'GRANT CREATE SESSION TO ' || DBMS_ASSERT.SQL_OBJECT_NAME(l_username);
DBMS_OUTPUT.PUT_LINE('User ' || l_username || ' created with CREATE SESSION privilege.');
ELSE
DBMS_OUTPUT.PUT_LINE('User ' || l_username || ' already exists.');
END IF;
EXCEPTION
WHEN OTHERS THEN
l_error_message := 'Error in Step 3 (Check/Create User): ' || SQLERRM;
DBMS_OUTPUT.PUT_LINE(l_error_message);
RAISE;
END;

-- Step 4: Create Pending Area
DBMS_OUTPUT.PUT_LINE('Step 4: Creating pending area...');
BEGIN
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_OUTPUT.PUT_LINE('Pending area created.');
EXCEPTION
WHEN OTHERS THEN
l_error_message := 'Error in Step 4 (Create Pending Area): ' || SQLERRM;
DBMS_OUTPUT.PUT_LINE(l_error_message);
RAISE;
END;

-- Step 5: Grant Switch Privilege to USER_A
DBMS_OUTPUT.PUT_LINE('Step 5: Granting switch privilege to ' || l_username || ' for LOW_GROUP...');
BEGIN
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
GRANTEE_NAME => l_username,
CONSUMER_GROUP => 'LOW_GROUP',
GRANT_OPTION => FALSE
);
DBMS_OUTPUT.PUT_LINE('Switch privilege granted to ' || l_username || ' for LOW_GROUP.');
EXCEPTION
WHEN OTHERS THEN
l_error_message := 'Error in Step 5 (Grant Switch Privilege): ' || SQLERRM;
DBMS_OUTPUT.PUT_LINE(l_error_message);
RAISE;
END;

-- Step 6: Map USER_A to LOW_GROUP
DBMS_OUTPUT.PUT_LINE('Step 6: Mapping ' || l_username || ' to LOW_GROUP...');
BEGIN
SELECT COUNT(*) INTO l_mapping_exists
FROM dba_rsrc_group_mappings
WHERE attribute = 'ORACLE_USER' AND value = UPPER(l_username);
IF l_mapping_exists = 0 THEN
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
ATTRIBUTE => DBMS_RESOURCE_MANAGER.ORACLE_USER,
VALUE => UPPER(l_username),
CONSUMER_GROUP => 'LOW_GROUP'
);
DBMS_OUTPUT.PUT_LINE('Mapping created for ' || l_username || ' to LOW_GROUP.');
ELSE
DBMS_OUTPUT.PUT_LINE('Mapping for ' || l_username || ' already exists.');
END IF;
EXCEPTION
WHEN OTHERS THEN
l_error_message := 'Error in Step 6 (Map User): ' || SQLERRM;
DBMS_OUTPUT.PUT_LINE(l_error_message);
RAISE;
END;

-- Step 7: Validate Pending Area
DBMS_OUTPUT.PUT_LINE('Step 7: Validating pending area...');
BEGIN
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_OUTPUT.PUT_LINE('Pending area validated successfully.');
EXCEPTION
WHEN OTHERS THEN
l_error_message := 'Error in Step 7 (Validate Pending Area): ' || SQLERRM;
DBMS_OUTPUT.PUT_LINE(l_error_message);
RAISE;
END;

-- Step 8: Submit Pending Area
DBMS_OUTPUT.PUT_LINE('Step 8: Submitting pending area...');
BEGIN
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
DBMS_OUTPUT.PUT_LINE('Pending area submitted successfully.');
EXCEPTION
WHEN OTHERS THEN
l_error_message := 'Error in Step 8 (Submit Pending Area): ' || SQLERRM;
DBMS_OUTPUT.PUT_LINE(l_error_message);
RAISE;
END;

-- Step 9: Ensure PLAN1 is Active
DBMS_OUTPUT.PUT_LINE('Step 9: Ensuring PLAN1 is active...');
BEGIN
IF plan_exists('PLAN1') THEN
EXECUTE IMMEDIATE 'ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = ''PLAN1'' SCOPE=BOTH';
DBMS_OUTPUT.PUT_LINE('PLAN1 set as active resource plan.');
END IF;
EXCEPTION
WHEN OTHERS THEN
l_error_message := 'Error in Step 9 (Set PLAN1): ' || SQLERRM;
DBMS_OUTPUT.PUT_LINE(l_error_message);
RAISE;
END;

-- Step 10: Verify Configuration
DBMS_OUTPUT.PUT_LINE('Step 10: Verifying configuration...');
BEGIN
SELECT COUNT(*) INTO l_mapping_exists
FROM dba_rsrc_group_mappings
WHERE attribute = 'ORACLE_USER' AND value = UPPER(l_username) AND consumer_group = 'LOW_GROUP';
IF l_mapping_exists > 0 THEN
DBMS_OUTPUT.PUT_LINE('Verified: ' || l_username || ' mapped to LOW_GROUP.');
ELSE
DBMS_OUTPUT.PUT_LINE('Warning: ' || l_username || ' mapping to LOW_GROUP not found.');
END IF;
EXCEPTION
WHEN OTHERS THEN
l_error_message := 'Error in Step 10 (Verify Configuration): ' || SQLERRM;
DBMS_OUTPUT.PUT_LINE(l_error_message);
RAISE;
END;

DBMS_OUTPUT.PUT_LINE('Successfully added ' || l_username || ' to LOW_GROUP under PLAN1.');

EXCEPTION
WHEN OTHERS THEN
-- Clear pending area on error to avoid locking
BEGIN
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
DBMS_OUTPUT.PUT_LINE('Pending area cleared due to error.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error clearing pending area: ' || SQLERRM);
END;
RAISE_APPLICATION_ERROR(-20001, 'Critical error: ' || l_error_message);
END;
/

++
++++++++++++++++++++++++++++++++++++++++++++
/* backup existing:

PL/SQL Script for Backup

This anonymous PL/SQL block uses DBMS_METADATA to extract DDLs for the Resource Manager configuration and writes them to a text file in DATA_PUMP_DIR. It captures the plan, consumer groups, directives, mappings, and system parameter, ensuring all components are preserved for reference or restoration. */

++++++++++++++++++++++++++++++++++++++++++++
SET SERVEROUTPUT ON;
-- Assumptions:
-- 1. Running on Oracle RDS with Enterprise Edition.
-- 2. User has ADMINISTER_RESOURCE_MANAGER and SELECT_CATALOG_ROLE or DBA privileges.
-- 3. DATA_PUMP_DIR exists and is writable.
-- 4. Backs up PLAN1, consumer groups (LOW_GROUP, HIGH_GROUP, LIMITED_USER_GROUP, OTHER_GROUPS),
--    directives, mappings, and RESOURCE_MANAGER_PLAN parameter.
-- 5. Output is a SQL file in DATA_PUMP_DIR for reference or restoration.
-- 6. No S3 integration or database bounce required.
DECLARE
  l_file_handle UTL_FILE.FILE_TYPE;
  l_file_name VARCHAR2(100) := 'resource_manager_backup_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MI') || '.sql';
  l_ddl CLOB;
  l_count NUMBER := 0;
  l_error_message VARCHAR2(4000);

  -- Cursor for consumer groups (excluding system groups like SYS_GROUP)
  CURSOR c_consumer_groups IS
    SELECT consumer_group
    FROM dba_rsrc_consumer_groups
    WHERE consumer_group IN ('LOW_GROUP', 'HIGH_GROUP', 'LIMITED_USER_GROUP', 'OTHER_GROUPS');

  -- Cursor for plan directives
  CURSOR c_directives IS
    SELECT plan, group_or_subplan
    FROM dba_rsrc_plan_directives
    WHERE plan = 'PLAN1';

  -- Cursor for consumer group mappings
  CURSOR c_mappings IS
    SELECT attribute, value, consumer_group
    FROM dba_rsrc_group_mappings
    WHERE attribute = 'ORACLE_USER';

BEGIN
  DBMS_OUTPUT.PUT_LINE('Starting Resource Manager backup to ' || l_file_name || '...');

  -- Step 1: Open File in DATA_PUMP_DIR
  DBMS_OUTPUT.PUT_LINE('Step 1: Opening backup file in DATA_PUMP_DIR...');
  BEGIN
    l_file_handle := UTL_FILE.FOPEN('DATA_PUMP_DIR', l_file_name, 'W', 32767);
    UTL_FILE.PUT_LINE(l_file_handle, '-- Resource Manager Backup created on ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
    UTL_FILE.PUT_LINE(l_file_handle, '-- For PLAN1, consumer groups, directives, and mappings');
    UTL_FILE.PUT_LINE(l_file_handle, 'SET SERVEROUTPUT ON;');
    UTL_FILE.PUT_LINE(l_file_handle, 'BEGIN');
    DBMS_OUTPUT.PUT_LINE('File opened successfully.');
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 1 (Open File): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      RAISE;
  END;

  -- Step 2: Backup Resource Plan (PLAN1)
  DBMS_OUTPUT.PUT_LINE('Step 2: Backing up PLAN1...');
  BEGIN
    SELECT COUNT(*) INTO l_count
    FROM dba_rsrc_plans
    WHERE plan = 'PLAN1';
    IF l_count > 0 THEN
      l_ddl := DBMS_METADATA.GET_DDL('RESOURCE_PLAN', 'PLAN1');
      UTL_FILE.PUT_LINE(l_file_handle, '-- Resource Plan: PLAN1');
      UTL_FILE.PUT_LINE(l_file_handle, l_ddl || ';');
      UTL_FILE.PUT_LINE(l_file_handle, '/');
      DBMS_OUTPUT.PUT_LINE('PLAN1 backed up.');
    ELSE
      UTL_FILE.PUT_LINE(l_file_handle, '-- Note: PLAN1 does not exist.');
      DBMS_OUTPUT.PUT_LINE('PLAN1 does not exist; skipping.');
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 2 (Backup PLAN1): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      UTL_FILE.PUT_LINE(l_file_handle, '-- Error backing up PLAN1: ' || SQLERRM);
    END;

  -- Step 3: Backup Consumer Groups
  DBMS_OUTPUT.PUT_LINE('Step 3: Backing up consumer groups...');
  l_count := 0;
  FOR cg IN c_consumer_groups LOOP
    BEGIN
      l_ddl := DBMS_METADATA.GET_DDL('CONSUMER_GROUP', cg.consumer_group);
      UTL_FILE.PUT_LINE(l_file_handle, '-- Consumer Group: ' || cg.consumer_group);
      UTL_FILE.PUT_LINE(l_file_handle, l_ddl || ';');
      UTL_FILE.PUT_LINE(l_file_handle, '/');
      l_count := l_count + 1;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error backing up consumer group ' || cg.consumer_group || ': ' || SQLERRM);
        UTL_FILE.PUT_LINE(l_file_handle, '-- Error backing up ' || cg.consumer_group || ': ' || SQLERRM);
    END;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(l_count || ' consumer groups backed up.');

  -- Step 4: Backup Plan Directives
  DBMS_OUTPUT.PUT_LINE('Step 4: Backing up plan directives for PLAN1...');
  l_count := 0;
  FOR d IN c_directives LOOP
    BEGIN
      l_ddl := DBMS_METADATA.GET_DDL('PLAN_DIRECTIVE', d.group_or_subplan, d.plan);
      UTL_FILE.PUT_LINE(l_file_handle, '-- Plan Directive for ' || d.plan || '.' || d.group_or_subplan);
      UTL_FILE.PUT_LINE(l_file_handle, l_ddl || ';');
      UTL_FILE.PUT_LINE(l_file_handle, '/');
      l_count := l_count + 1;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error backing up directive ' || d.plan || '.' || d.group_or_subplan || ': ' || SQLERRM);
        UTL_FILE.PUT_LINE(l_file_handle, '-- Error backing up directive ' || d.plan || '.' || d.group_or_subplan || ': ' || SQLERRM);
    END;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(l_count || ' plan directives backed up.');

  -- Step 5: Backup Consumer Group Mappings
  DBMS_OUTPUT.PUT_LINE('Step 5: Backing up consumer group mappings...');
  l_count := 0;
  FOR m IN c_mappings LOOP
    BEGIN
      UTL_FILE.PUT_LINE(l_file_handle, '-- Consumer Group Mapping for ' || m.attribute || ' = ' || m.value);
      UTL_FILE.PUT_LINE(l_file_handle, 'BEGIN');
      UTL_FILE.PUT_LINE(l_file_handle, '  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(');
      UTL_FILE.PUT_LINE(l_file_handle, '    ATTRIBUTE => DBMS_RESOURCE_MANAGER.' || m.attribute || ',');
      UTL_FILE.PUT_LINE(l_file_handle, '    VALUE => ''' || m.value || ''',');
      UTL_FILE.PUT_LINE(l_file_handle, '    CONSUMER_GROUP => ''' || m.consumer_group || '''');
      UTL_FILE.PUT_LINE(l_file_handle, '  );');
      UTL_FILE.PUT_LINE(l_file_handle, 'END;');
      UTL_FILE.PUT_LINE(l_file_handle, '/');
      l_count := l_count + 1;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error backing up mapping for ' || m.value || ': ' || SQLERRM);
        UTL_FILE.PUT_LINE(l_file_handle, '-- Error backing up mapping for ' || m.value || ': ' || SQLERRM);
    END;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(l_count || ' mappings backed up.');

  -- Step 6: Backup RESOURCE_MANAGER_PLAN Parameter
  DBMS_OUTPUT.PUT_LINE('Step 6: Backing up RESOURCE_MANAGER_PLAN parameter...');
  BEGIN
    SELECT value INTO l_ddl
    FROM v$parameter
    WHERE name = 'resource_manager_plan';
    UTL_FILE.PUT_LINE(l_file_handle, '-- Current RESOURCE_MANAGER_PLAN setting');
    UTL_FILE.PUT_LINE(l_file_handle, 'ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = ''' || NVL(l_ddl, '') || ''' SCOPE=BOTH;');
    UTL_FILE.PUT_LINE(l_file_handle, '/');
    DBMS_OUTPUT.PUT_LINE('RESOURCE_MANAGER_PLAN parameter backed up: ' || NVL(l_ddl, 'not set'));
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 6 (Backup Parameter): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      UTL_FILE.PUT_LINE(l_file_handle, '-- Error backing up RESOURCE_MANAGER_PLAN: ' || SQLERRM);
    END;

  -- Step 7: Close File
  DBMS_OUTPUT.PUT_LINE('Step 7: Closing backup file...');
  BEGIN
    UTL_FILE.PUT_LINE(l_file_handle, 'END;');
    UTL_FILE.PUT_LINE(l_file_handle, '/');
    UTL_FILE.PUT_LINE(l_file_handle, '-- Backup completed on ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
    UTL_FILE.FCLOSE(l_file_handle);
    DBMS_OUTPUT.PUT_LINE('Backup file ' || l_file_name || ' created in DATA_PUMP_DIR.');
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'Error in Step 7 (Close File): ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(l_error_message);
      IF UTL_FILE.IS_OPEN(l_file_handle) THEN
        UTL_FILE.FCLOSE(l_file_handle);
      END IF;
      RAISE;
  END;

EXCEPTION
  WHEN OTHERS THEN
    l_error_message := 'Critical error in backup process: ' || SQLERRM;
    DBMS_OUTPUT.PUT_LINE(l_error_message);
    IF UTL_FILE.IS_OPEN(l_file_handle) THEN
      UTL_FILE.FCLOSE(l_file_handle);
    END IF;
    RAISE_APPLICATION_ERROR(-20001, l_error_message);
END;
/



++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Usage Notes

  1. Execution:
    • Run the script in SQL*Plus, SQL Developer, or any SQL client as the RDS master user or a user with ADMINISTER_RESOURCE_MANAGER and SELECT_CATALOG_ROLE privileges.
    • The script creates a file named resource_manager_backup_YYYYMMDD_HH24MI.sql in DATA_PUMP_DIR.
    • Example output file:
      sql
      -- Resource Manager Backup created on 2025-08-21 13:06:00
      SET SERVEROUTPUT ON;
      BEGIN
      -- Resource Plan: PLAN1
      EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'PLAN1', MGMT_MTH => 'RATIO', ...);
      /
      -- Consumer Group: LOW_GROUP
      EXEC DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'LOW_GROUP', ...);
      /
      -- Plan Directive for PLAN1.LOW_GROUP
      EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'PLAN1', GROUP_OR_SUBPLAN => 'LOW_GROUP', ...);
      /
      -- Consumer Group Mapping for ORACLE_USER = USER_A
      BEGIN
      DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
      ATTRIBUTE => DBMS_RESOURCE_MANAGER.ORACLE_USER,
      VALUE => 'USER_A',
      CONSUMER_GROUP => 'LIMITED_USER_GROUP'
      );
      END;
      /
      -- Current RESOURCE_MANAGER_PLAN setting
      ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'PLAN1' SCOPE=BOTH;
      /
      END;
      /
  2. Accessing the Backup File:
    • The file is stored in DATA_PUMP_DIR. To locate it:
      sql
      SELECT directory_path FROM dba_directories WHERE directory_name = 'DATA_PUMP_DIR';
    • In RDS, you cannot directly access the file system. Transfer the file using a client tool (e.g., SQL Developer) or AWS CLI:
      bash
      aws rds download-db-log-file-portion --db-instance-identifier your-rds-instance --log-file-name /path/to/DATA_PUMP_DIR/resource_manager_backup_YYYYMMDD_HH24MI.sql
      Replace the path and instance details accordingly.
    • Alternatively, read the file content in SQL:
      sql
      DECLARE
      l_file_handle UTL_FILE.FILE_TYPE;
      l_line VARCHAR2(32767);
      BEGIN
      l_file_handle := UTL_FILE.FOPEN('DATA_PUMP_DIR', 'resource_manager_backup_YYYYMMDD_HH24MI.sql', 'R', 32767);
      LOOP
      BEGIN
      UTL_FILE.GET_LINE(l_file_handle, l_line);
      DBMS_OUTPUT.PUT_LINE(l_line);
      EXCEPTION
      WHEN NO_DATA_FOUND THEN EXIT;
      END;
      END LOOP;
      UTL_FILE.FCLOSE(l_file_handle);
      END;
      /
  3. Restoring the Backup:
    • To restore the Resource Manager configuration (e.g., after unintended changes):
      • Open the backup file and execute its contents in SQL*Plus or SQL Developer.
      • Ensure you’re connected as a user with ADMINISTER_RESOURCE_MANAGER privileges.
      • Clear the pending area first:
        sql
        EXEC DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
      • Run the SQL file to recreate the plan, groups, directives, and mappings.
      • Reapply the RESOURCE_MANAGER_PLAN setting:
        sql
        ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'PLAN1' SCOPE=BOTH;
  2. Testing:
    • Verify the backup file exists:
      sql
      SELECT filename
      FROM TABLE(rdsadmin.rds_file_util.listdir('DATA_PUMP_DIR'))
      WHERE filename LIKE 'resource_manager_backup%';
    • Check the backed-up components:
      sql
      SELECT plan FROM dba_rsrc_plans WHERE plan = 'PLAN1';
      SELECT consumer_group FROM dba_rsrc_consumer_groups WHERE consumer_group IN ('LOW_GROUP', 'HIGH_GROUP', 'LIMITED_USER_GROUP', 'OTHER_GROUPS');
      SELECT plan, group_or_subplan FROM dba_rsrc_plan_directives WHERE plan = 'PLAN1';
      SELECT attribute, value, consumer_group FROM dba_rsrc_group_mappings WHERE attribute = 'ORACLE_USER';
    • Compare with the backup file content to ensure all components are captured.
  3. RDS-Specific:
    • No database bounce is required; the backup is purely SQL-based.
    • The script uses DATA_PUMP_DIR, which is RDS-compatible.
    • File transfer requires a client tool due to RDS’s lack of direct file system access.
    • No S3 integration or database links are used, per your preference.
  4. Error Handling:
    • The script logs errors for each component (plan, groups, directives, mappings) and continues to ensure a complete backup.
    • Uses DBMS_METADATA for reliable DDL extraction.
    • Closes the file on errors to avoid resource leaks.
    • Validates DATA_PUMP_DIR access before writing.
  5. Restoration Considerations:
    • If PLAN1 or groups are modified after the backup, restoring may overwrite changes. Check current settings before restoration:
      sql
      SELECT plan, group_or_subplan, mgmt_p1, utilization_limit
      FROM dba_rsrc_plan_directives
      WHERE plan = 'PLAN1';
    • To avoid conflicts, clear existing configurations if restoring:
      sql
      BEGIN
      DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
      DBMS_RESOURCE_MANAGER.DELETE_PLAN_CASCADE('PLAN1');
      DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
      DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
      END;
      /

This script provides a robust, RDS-compatible backup of your Resource Manager configuration, capturing all necessary components in a SQL file for easy reference or restoration. Test the backup process in a non-production environment to ensure the file is created and accessible.


++++++++++++

explanation:

++++++++++++++


1) What Happens When User ABC, User 123, and User789 Kick In at the Same Time?

Imagine your Oracle database is like a pizza that's being shared among different groups of friends (the consumer groups). The "RATIO" method is like dividing the pizza based on pre-set shares (MGMT_P1 weights), but with caps (UTILIZATION_LIMIT) so no one takes too much.

  • User ABC and User 123 are in the LOW_GROUP, which has a small share of the pizza (MGMT_P1 = 20) and a cap of 30% total CPU.
  • User789 is in the LIMITED_USER_GROUP, which has a big share (MGMT_P1 = 70) and a cap of 70% CPU.
  • There are also HIGH_GROUP (small share, 5%, cap 10%) and OTHER_GROUPS (small share, 5%, no cap) for any other users.

When all three users start working at the same time and the database gets busy (CPU hits 100% usage):

  • The database first checks if the total CPU is "full" (CPU-bound). If not, everyone gets what they need without limits.
  • If it is full, the pizza (CPU) is divided roughly like this:
    • LIMITED_USER_GROUP (user789) gets about 70% of the CPU share, but capped at 70% max.
    • LOW_GROUP (user ABC and user 123) gets about 20% share, but capped at 30% max. Since there are two users in LOW_GROUP, they have to split that 20% between them—if both are demanding a lot, the database might slow one down to share fairly within the group.
    • HIGH_GROUP and OTHER_GROUPS get the remaining ~10% (5% each), shared among their users.
  • In practice: User789 (in the "big" group) will likely run smoothly with up to 70% CPU, while user ABC and user 123 (in the "small" group) might slow down or wait if they need more than their 20% share allows. The system prioritizes user789's group because of the higher MGMT_P1 weight.

Bottom line: The database won't crash, but the LOW_GROUP users (ABC and 123) might feel the squeeze if user789 is hogging CPU, like waiting in line for a smaller slice of pizza.

2) What Happens to the Other Sessions When They Are Performing High CPU Intensive Tasks When Step 1 Users Are Active?

"Other sessions" means users not in LOW_GROUP or LIMITED_USER_GROUP—like those in HIGH_GROUP, OTHER_GROUPS, or unmapped sessions.

  • When LOW_GROUP (user ABC, user 123) and LIMITED_USER_GROUP (user789) are active and doing heavy work (high CPU tasks):
    • The database checks if CPU is maxed out. If not, all sessions (including "other" ones) run normally.
    • If CPU is maxed, the RATIO method kicks in: CPU is divided by MGMT_P1 weights.
  • For other sessions:
    • HIGH_GROUP users: They get a tiny share (5%) and are capped at 10% CPU. If they're running high-CPU tasks, they'll slow down a lot or queue up, because the bigger groups (LIMITED_USER_GROUP at 70%, LOW_GROUP at 20%) take most of the CPU. It's like they're last in line for the pizza.
    • OTHER_GROUPS or unmapped users: Even tinier share (5%), no cap. High-CPU tasks here will throttle severely, waiting for scraps of CPU after the main groups are served.
  • Result: Other sessions might "hang" or run very slowly during peak times, as the system prioritizes LOW_GROUP and LIMITED_USER_GROUP. This could affect background jobs, admins (e.g., SYS in OTHER_GROUPS), or other apps if they're CPU-hungry.

In simple terms: The "other" folks get pushed to the back when the main users (from step 1) are busy—think of a crowded buffet where big groups grab most of the food first.

3) How Is the Load Distributed?

Load distribution (CPU sharing) in your PLAN1 is like dividing a fixed pie (the database's CPU) among groups, with rules for fairness and caps to prevent greediness.

  • When CPU Is Not Busy (Under 100%): No limits—everyone gets what they ask for. Load is distributed based on demand, no queuing or slowing.
  • When CPU Is Busy (100% Usage): The RATIO method divides CPU like this:
    • Calculate shares based on MGMT_P1 weights (total weights = 100 in the script: 70 for LIMITED_USER_GROUP, 20 for LOW_GROUP, 5 for HIGH_GROUP, 5 for OTHER_GROUPS).
    • Example distribution:
      • LIMITED_USER_GROUP: Gets ~70% of CPU (e.g., user789 runs fast for heavy tasks).
      • LOW_GROUP: Gets ~20% (split between user ABC and user 123; if both are active, each might get ~10%).
      • HIGH_GROUP: Gets ~5% (shared among its users; slow for CPU-heavy work).
      • OTHER_GROUPS: Gets ~5% (for everything else; very limited).
    • Then, apply caps (UTILIZATION_LIMIT):
      • LOW_GROUP can't exceed 30% total, even if more is available.
      • LIMITED_USER_GROUP can't exceed 70%.
      • HIGH_GROUP can't exceed 10%.
      • OTHER_GROUPS has no cap, but its small share limits it anyway.
  • Within a Group: If multiple users (e.g., ABC and 123 in LOW_GROUP) are in the same group, they share the group's allocation fairly—Oracle round-robins CPU time among them.
  • Queuing: If a group exceeds its share, tasks queue (wait) based on FIFO_TIMEOUT (first-in-first-out with timeout), potentially timing out if too long.

In layman terms: It's like a family sharing a TV remote—the big kid (LIMITED_USER_GROUP) gets 70% control time, the medium kids (LOW_GROUP) get 20%, and the little ones (HIGH_GROUP/OTHER_GROUPS) get just 5% each. Caps ensure no one hogs it all, but if everyone wants the remote at once, the smaller shares wait or get frustrated.


+++++++++++++

impact analysis

+++++++++++++


Let’s dive into a detailed explanation of the existing Oracle Database Resource Manager setup for your `PLAN1` in an Oracle RDS environment, focusing on the `RATIO` management method and the impact of the provided PL/SQL script when adding a new consumer group (`LIMITED_USER_GROUP`) with a 70% CPU limit for `USER_A`. I’ll break down the existing setup, how the script modifies it, the implications of these changes, and how they affect CPU allocation, particularly under the `RATIO` method. This will clarify the potential impact on existing functionality, as you’ve asked, and ensure a clear understanding of how CPU resources are managed. ### Existing Setup: Understanding `PLAN1` and `RATIO` Method Based on the partial code you provided, your existing Resource Manager configuration includes: - **Plan**: `PLAN1`, which uses the `RATIO` management method for CPU allocation, along with `ACTIVE_SESS_POOL_ABSOLUTE`, `PARALLEL_DEGREE_LIMIT_ABSOLUTE`, and `FIFO_TIMEOUT` queuing method. - **Consumer Groups**: - `LOW_GROUP`: Has a directive with `UTILIZATION_LIMIT => 30`, capping its CPU usage at 30% of available CPU resources when the database is CPU-bound. The `MGMT_P1` value (weight for CPU allocation under `RATIO`) is not specified in your code. - `HIGH_GROUP`: Has a directive, but no `UTILIZATION_LIMIT` or `MGMT_P1` is specified, suggesting it may have unrestricted CPU access or a different allocation not shown. - `OTHER_GROUPS`: Has a minimal directive (required by Oracle to handle unmapped sessions), but no specific `MGMT_P1` or `UTILIZATION_LIMIT` is provided. - **RATIO Management Method**: - In Oracle Resource Manager, the `RATIO` method allocates CPU resources among consumer groups based on the `MGMT_P1` values (Level 1 management weights) when the database is CPU-bound (i.e., total CPU usage reaches 100%). - `MGMT_P1` represents a relative weight, not a percentage. For example, if `LOW_GROUP` has `MGMT_P1 => 30` and `HIGH_GROUP` has `MGMT_P1 => 70`, the CPU is split approximately 30:70 when both groups are active and competing for CPU. - The sum of `MGMT_P1` values across all groups at Level 1 doesn’t need to equal 100, but Oracle normalizes them to determine proportional shares. For instance, `MGMT_P1` values of 30, 50, and 20 for three groups result in shares of 30/(30+50+20) = 30%, 50%, and 20%, respectively. - **UTILIZATION_LIMIT**: - This sets an absolute cap on CPU usage for a consumer group, regardless of `MGMT_P1`. For `LOW_GROUP`, `UTILIZATION_LIMIT => 30` means it cannot exceed 30% of total CPU, even if more is available. - Without a `UTILIZATION_LIMIT`, a group (e.g., `HIGH_GROUP`) can consume all available CPU if other groups are capped or idle. - **RDS Context**: Oracle RDS supports Resource Manager, but CPU limits are enforced only when the instance is CPU-bound (monitored via AWS CloudWatch). The `RATIO` method and `UTILIZATION_LIMIT` work together to manage contention. Since your code doesn’t specify `MGMT_P1` for `LOW_GROUP`, `HIGH_GROUP`, or `OTHER_GROUPS`, I’ll assume: - `LOW_GROUP` has a default or unspecified `MGMT_P1` (e.g., 100, common default in `RATIO` if not set). - `HIGH_GROUP` likely has a higher `MGMT_P1` or no limit, prioritizing its sessions. - `OTHER_GROUPS` has a minimal `MGMT_P1` to avoid errors like ORA-29363. ### Script Impact: Adding `LIMITED_USER_GROUP` with 70% CPU Limit The provided PL/SQL script modifies `PLAN1` to add `USER_A` to a new consumer group (`LIMITED_USER_GROUP`) with a 70% CPU limit. Here’s a detailed breakdown of the script’s changes and their impact: #### 1. **Script Changes** The script performs the following: - **Checks `PLAN1` Existence** (Step 1): - Uses the corrected `plan_exists` function to verify `PLAN1` exists in `dba_rsrc_plans` with `NVL(status, 'ACTIVE') != 'PENDING'`. - If `PLAN1` doesn’t exist, it raises an error to prevent proceeding, as your setup assumes `PLAN1` is already in place. - **Checks/Creates `LIMITED_USER_GROUP`** (Step 3): - Verifies if `LIMITED_USER_GROUP` exists in `dba_rsrc_consumer_groups`. - If not, creates it with a comment indicating a 70% CPU limit. - **Configures Directives** (Step 4): - **LIMITED_USER_GROUP**: Creates or updates a directive with: - `UTILIZATION_LIMIT => 70`: Caps CPU usage at 70% of total CPU. - `MGMT_P1 => 70`: Assigns a weight of 70 for CPU allocation under the `RATIO` method. - Matches `PLAN1`’s methods: `RATIO`, `ACTIVE_SESS_POOL_ABSOLUTE`, `PARALLEL_DEGREE_LIMIT_ABSOLUTE`, `FIFO_TIMEOUT`. - **LOW_GROUP**: Updates the existing directive to: - `MGMT_P1 => 20`: Reduces its CPU allocation weight to balance with `LIMITED_USER_GROUP`. - `UTILIZATION_LIMIT => 30`: Retains the existing 30% cap. - **HIGH_GROUP** (if exists): Updates to: - `MGMT_P1 => 5`: Assigns a minimal weight to prioritize `LIMITED_USER_GROUP`. - `UTILIZATION_LIMIT => 10`: Adds a cap to prevent unrestricted CPU use, ensuring room for `LIMITED_USER_GROUP`. - **OTHER_GROUPS**: Creates or updates a minimal directive: - `MGMT_P1 => 5`: Minimal weight to satisfy Oracle’s requirement. - Matches `PLAN1`’s methods. - **Maps Users** (Step 5): - Adds `USER_A` (and optional users) to `LIMITED_USER_GROUP` via `GRANT_SWITCH_CONSUMER_GROUP` and `SET_CONSUMER_GROUP_MAPPING`. - Creates users if they don’t exist with minimal privileges (`CREATE SESSION`). - **Enables Plan** (Step 8): - Sets `RESOURCE_MANAGER_PLAN = 'PLAN1' SCOPE=BOTH` to ensure `PLAN1` is active and persists across RDS restarts. - **Verification** (Step 9): - Confirms `PLAN1` is active and users are mapped to `LIMITED_USER_GROUP`. #### 2. **Impact on CPU Allocation** The script’s changes affect how CPU is distributed under `PLAN1` when the database is CPU-bound: - **Before Script**: - Assume `LOW_GROUP` has `UTILIZATION_LIMIT => 30` and an unspecified `MGMT_P1` (e.g., 100). - `HIGH_GROUP` likely has a higher `MGMT_P1` (e.g., 200) or no limit, giving it priority. - `OTHER_GROUPS` has a minimal `MGMT_P1` (e.g., 10). - CPU shares under `RATIO` are proportional to `MGMT_P1`. If `MGMT_P1` values are 100:200:10 for `LOW_GROUP`, `HIGH_GROUP`, `OTHER_GROUPS`, shares are approximately 31% (100/310), 62% (200/310), 3% (10/310), but `LOW_GROUP` is capped at 30%. - **After Script**: - New directive: `LIMITED_USER_GROUP` with `MGMT_P1 => 70`, `UTILIZATION_LIMIT => 70`. - Updated directives: - `LOW_GROUP`: `MGMT_P1 => 20`, `UTILIZATION_LIMIT => 30`. - `HIGH_GROUP`: `MGMT_P1 => 5`, `UTILIZATION_LIMIT => 10`. - `OTHER_GROUPS`: `MGMT_P1 => 5`. - New `MGMT_P1` total: 70 + 20 + 5 + 5 = 100. - CPU shares under `RATIO`: - `LIMITED_USER_GROUP`: 70/100 = 70%, capped at 70%. - `LOW_GROUP`: 20/100 = 20%, capped at 30%. - `HIGH_GROUP`: 5/100 = 5%, capped at 10%. - `OTHER_GROUPS`: 5/100 = 5%, no cap. - **Key Change**: `LIMITED_USER_GROUP` gets the largest CPU share (70%) when CPU-bound, reducing shares for `LOW_GROUP` (from ~31% to 20%) and `HIGH_GROUP` (from ~62% to 5%). #### 3. **Potential Impact on Existing Functionality** The script is designed to minimize disruption, but here are the potential impacts: - **CPU Redistribution**: - **LOW_GROUP**: Previously, it could consume up to 30% CPU (limited by `UTILIZATION_LIMIT`). The new `MGMT_P1 => 20` reduces its priority relative to `LIMITED_USER_GROUP`, meaning it gets less CPU when competing with `USER_A`. However, its 30% cap remains unchanged, so the impact is minimal unless `LOW_GROUP` users were relying on higher CPU shares. - **HIGH_GROUP**: If previously unrestricted or with a high `MGMT_P1`, setting `MGMT_P1 => 5` and `UTILIZATION_LIMIT => 10` significantly reduces its CPU access. This could degrade performance for `HIGH_GROUP` users (e.g., critical applications) if they were prioritized. - **OTHER_GROUPS**: The minimal `MGMT_P1 => 5` may reduce CPU for unmapped users (e.g., `SYS`, other users), potentially causing throttling if they were consuming significant CPU. - **Mitigation**: Check current `MGMT_P1` values: ```sql SELECT plan, group_or_subplan, mgmt_p1, utilization_limit FROM dba_rsrc_plan_directives WHERE plan = 'PLAN1'; ``` If `HIGH_GROUP` needs more CPU, adjust `MGMT_P1` in Step 4 (e.g., `HIGH_GROUP => 15`, `OTHER_GROUPS => 5`, `LOW_GROUP => 10`, keeping `LIMITED_USER_GROUP => 70`). - **Plan Activation**: - If `PLAN1` is not the active plan, enabling it via `ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'PLAN1' SCOPE=BOTH` overrides the current plan, potentially changing resource allocation for all users. - **Mitigation**: Verify the active plan: ```sql SHOW PARAMETER RESOURCE_MANAGER_PLAN; ``` Save the current plan for rollback: ```sql SELECT value FROM v$parameter WHERE name = 'resource_manager_plan'; ``` - **Session Mapping**: - Adding `USER_A` to `LIMITED_USER_GROUP` doesn’t affect other users’ mappings unless they’re in the input list (`l_users`). - Existing sessions for `USER_A` won’t adopt the new group until reconnected. - **Mitigation**: Reconnect `USER_A` sessions: ```sql SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;' AS kill_cmd FROM V$SESSION WHERE username = 'USER_A'; ``` - **Performance**: - `LIMITED_USER_GROUP`’s 70% CPU priority may throttle `HIGH_GROUP` and `OTHER_GROUPS` when CPU-bound, especially since `HIGH_GROUP` is now capped at 10%. - **Mitigation**: Monitor CPU usage in CloudWatch (RDS > Monitoring > CPUUtilization) and Resource Manager: ```sql SELECT name, active_sessions, cpu_wait_time, consumed_cpu_time FROM V$RSRC_CONSUMER_GROUP WHERE name IN ('LOW_GROUP', 'HIGH_GROUP', 'LIMITED_USER_GROUP', 'OTHER_GROUPS'); ``` #### 4. **How the Script Minimizes Disruption** - **Checks Existing Objects**: Uses `plan_exists` and queries to avoid duplicating `PLAN1` or `LIMITED_USER_GROUP`. - **Preserves Methods**: Matches `PLAN1`’s `RATIO`, `ACTIVE_SESS_POOL_ABSOLUTE`, `PARALLEL_DEGREE_LIMIT_ABSOLUTE`, and `FIFO_TIMEOUT` for new directives. - **Conservative Adjustments**: Updates `MGMT_P1` to balance CPU (total 100), minimizing impact while prioritizing `LIMITED_USER_GROUP`. - **Error Handling**: Validates the pending area, logs errors per user, and clears the pending area on failure to avoid locking. - **No Bounce Required**: Uses `SCOPE=BOTH` for dynamic, persistent changes. - **User-Specific**: Only maps specified users (e.g., `USER_A`) to `LIMITED_USER_GROUP`, leaving others intact. #### 5. **Recommendations to Avoid Breaking Functionality** - **Pre-Run Validation**: - Check current directives and mappings: ```sql SELECT plan, group_or_subplan, mgmt_p1, utilization_limit FROM dba_rsrc_plan_directives WHERE plan = 'PLAN1'; SELECT attribute, value, consumer_group FROM dba_rsrc_group_mappings; ``` - Confirm `PLAN1` is active: ```sql SHOW PARAMETER RESOURCE_MANAGER_PLAN; ``` - **Adjust MGMT_P1**: If `HIGH_GROUP` is critical, modify Step 4 to allocate more `MGMT_P1` (e.g., `HIGH_GROUP => 15`, `OTHER_GROUPS => 5`, `LOW_GROUP => 10`, `LIMITED_USER_GROUP => 70`). - **Test in Non-Production**: Run the script in a test RDS instance to assess impact on `HIGH_GROUP` and other users. - **Monitor Post-Deployment**: - Test with `USER_A`: ```sql SELECT /*+ PARALLEL(4) */ COUNT(*) FROM dba_objects, dba_objects; ``` - Verify consumer group: ```sql SELECT sid, username, resource_consumer_group FROM V$SESSION WHERE username = 'USER_A'; ``` - Check CloudWatch and Resource Manager metrics. - **Rollback Plan**: If issues arise, revert changes: ```sql BEGIN DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA(); DBMS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP_MAPPING( ATTRIBUTE => DBMS_RESOURCE_MANAGER.ORACLE_USER, VALUE => 'USER_A' ); -- Restore original MGMT_P1 values (from pre-run check) DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE( PLAN => 'PLAN1', GROUP_OR_SUBPLAN => 'LOW_GROUP', NEW_MGMT_P1 => original_value, NEW_UTILIZATION_LIMIT => 30 ); DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE( PLAN => 'PLAN1', GROUP_OR_SUBPLAN => 'HIGH_GROUP', NEW_MGMT_P1 => original_value, NEW_UTILIZATION_LIMIT => NULL ); DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA(); DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA(); END; / ``` ### Conclusion The script is unlikely to break existing functionality if tested carefully, as it preserves `PLAN1`’s structure and only adjusts necessary directives. However, the primary risk is CPU redistribution due to `MGMT_P1` changes, particularly reducing `HIGH_GROUP`’s allocation. By validating the current setup, adjusting `MGMT_P1` values as needed, and testing in a non-production environment, you can ensure the 70% CPU limit for `USER_A` integrates smoothly with `PLAN1` while minimizing impact on `LOW_GROUP`, `HIGH_GROUP`, and `OTHER_GROUPS`. No database bounce is needed, and the script is RDS-compatible with robust error handling.


+++++++

notes

+++++++

Adding a user (e.g., `USER_A`) to the existing Oracle Database Resource Manager plan (`PLAN1`) with a 70% CPU limit via a new consumer group (`LIMITED_USER_GROUP`), as outlined in the provided PL/SQL script, is generally safe but could potentially impact existing functionality in your Oracle RDS database depending on the current configuration and workload. Below, I’ll analyze the potential risks of breaking existing functionality, focusing on your specific setup (based on the partial code you provided for `PLAN1` with `LOW_GROUP`, `HIGH_GROUP`, and `OTHER_GROUPS`), and explain how the script mitigates these risks to ensure minimal disruption. I’ll also highlight scenarios where issues could arise and how to address them. ### Analysis of Potential Impact on Existing Functionality Your existing setup includes: - **Plan**: `PLAN1` with `RATIO` management method, `ACTIVE_SESS_POOL_ABSOLUTE`, `PARALLEL_DEGREE_LIMIT_ABSOLUTE`, and `FIFO_TIMEOUT` queuing method. - **Consumer Groups**: `LOW_GROUP` (30% CPU limit via `UTILIZATION_LIMIT`), `HIGH_GROUP` (no explicit CPU limit in your partial code), and `OTHER_GROUPS` (minimal directive). - **Goal**: Add `USER_A` to a new `LIMITED_USER_GROUP` with a 70% CPU limit under `PLAN1`. Here’s how adding `USER_A` to `PLAN1` could affect existing functionality: 1. **CPU Allocation Changes (RATIO Method)**: - **Existing Setup**: `PLAN1` uses the `RATIO` management method, where CPU is allocated based on `MGMT_P1` weights when the database is CPU-bound. Your partial code suggests `LOW_GROUP` has a 30% `UTILIZATION_LIMIT` but doesn’t specify `MGMT_P1` for `LOW_GROUP`, `HIGH_GROUP`, or `OTHER_GROUPS`. - **Script Impact**: The script assigns `LIMITED_USER_GROUP` a 70% CPU limit (`UTILIZATION_LIMIT => 70`, `MGMT_P1 => 70`) and adjusts existing directives: - `LOW_GROUP`: Updates `MGMT_P1 => 20` (keeps 30% `UTILIZATION_LIMIT`). - `HIGH_GROUP` (if exists): Sets `MGMT_P1 => 5`, `UTILIZATION_LIMIT => 10`. - `OTHER_GROUPS`: Sets `MGMT_P1 => 5`. - **Potential Risk**: - Changing `MGMT_P1` values alters the CPU distribution for existing groups. For example, `LOW_GROUP` sessions may get less CPU priority (20 vs. potentially higher before) when CPU-bound, as `LIMITED_USER_GROUP` takes 70. `HIGH_GROUP` and `OTHER_GROUPS` get minimal shares (5 each), which could starve other users if they rely on higher CPU access. - If `HIGH_GROUP` previously had significant CPU access (no limit), capping it at 10% could degrade performance for its users. - **Mitigation**: - The script checks existing directives and updates them conservatively. - Verify current `MGMT_P1` values before running: ```sql SELECT plan, group_or_subplan, mgmt_p1, utilization_limit FROM dba_rsrc_plan_directives WHERE plan = 'PLAN1'; ``` Adjust `MGMT_P1` in the script if needed (e.g., increase `HIGH_GROUP` to 10 if critical). - Test in a non-production environment to assess impact on existing workloads. 2. **Session Behavior**: - **Existing Setup**: Users mapped to `LOW_GROUP`, `HIGH_GROUP`, or `OTHER_GROUPS` operate under their current directives. Unmapped users fall into `OTHER_GROUPS`. - **Script Impact**: Adds `USER_A` to `LIMITED_USER_GROUP`, which doesn’t affect other users’ mappings unless they’re explicitly reassigned. - **Potential Risk**: - If `USER_A` was previously mapped to another group (e.g., `HIGH_GROUP`), moving them to `LIMITED_USER_GROUP` caps their CPU at 70%, potentially reducing their performance. - Existing sessions for `USER_A` won’t adopt the new mapping until reconnected. - **Mitigation**: - The script checks for existing mappings to avoid duplicates. - Reconnect `USER_A` sessions after running: ```sql SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;' AS kill_cmd FROM V$SESSION WHERE username = 'USER_A'; ``` - Check current mappings: ```sql SELECT attribute, value, consumer_group FROM dba_rsrc_group_mappings WHERE value = 'USER_A'; ``` 3. **Plan and Directive Compatibility**: - **Existing Setup**: `PLAN1` uses `RATIO`, `ACTIVE_SESS_POOL_ABSOLUTE`, `PARALLEL_DEGREE_LIMIT_ABSOLUTE`, and `FIFO_TIMEOUT`. - **Script Impact**: The script preserves these methods for new directives, ensuring consistency with your setup. - **Potential Risk**: - If `PLAN1` has additional directives or subplans not mentioned, the script’s adjustments to `MGMT_P1` could disrupt their balance. - Oracle requires `OTHER_GROUPS` to have a directive; the script ensures this but sets a low `MGMT_P1` (5), which could limit unmapped users. - **Mitigation**: - The script uses your `plan_exists` function (corrected) to confirm `PLAN1` exists. - Review all directives before running: ```sql SELECT plan, group_or_subplan, mgmt_p1, utilization_limit, active_sess_pool_p1, parallel_degree_limit_p1 FROM dba_rsrc_plan_directives WHERE plan = 'PLAN1'; ``` - Adjust `MGMT_P1` values in the script to match your workload (ensure sum <= 100 for Level 1). 4. **RDS-Specific Constraints**: - **Existing Setup**: RDS supports Resource Manager but restricts OS access and some features (e.g., no database links). - **Script Impact**: The script is SQL-based, uses `SCOPE=BOTH` for persistence, and requires no bounce. - **Potential Risk**: - If `PLAN1` is not the active plan, enabling it via `ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'PLAN1' SCOPE=BOTH` could override another plan, affecting all users. - RDS parameter changes are persisted, but misconfiguration could require manual rollback. - **Mitigation**: - Check the current plan: ```sql SHOW PARAMETER RESOURCE_MANAGER_PLAN; ``` - If another plan is active, consider whether to switch or modify the script to use that plan. - Backup the current plan setting: ```sql SELECT value FROM v$parameter WHERE name = 'resource_manager_plan'; ``` - Rollback if needed: ```sql ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'previous_plan' SCOPE=BOTH; ``` 5. **Performance Impact**: - **Existing Setup**: `LOW_GROUP` (30% CPU limit) and `HIGH_GROUP` (unspecified limit) manage existing workloads. - **Script Impact**: Adding `LIMITED_USER_GROUP` with 70% CPU limit prioritizes its users when CPU-bound, potentially reducing CPU for `LOW_GROUP`, `HIGH_GROUP`, or `OTHER_GROUPS`. - **Potential Risk**: - Heavy CPU usage by `USER_A` could throttle other groups, especially `HIGH_GROUP` if it’s now capped at 10%. - The `RATIO` method’s `MGMT_P1` weights (70:20:5:5) may not align with your workload priorities. - **Mitigation**: - Monitor CPU usage in AWS CloudWatch (RDS > Monitoring > CPUUtilization) post-deployment. - Test with a CPU-intensive query as `USER_A`: ```sql SELECT /*+ PARALLEL(4) */ COUNT(*) FROM dba_objects, dba_objects; ``` - Check consumer group metrics: ```sql SELECT name, active_sessions, cpu_wait_time, consumed_cpu_time FROM V$RSRC_CONSUMER_GROUP WHERE name IN ('LOW_GROUP', 'HIGH_GROUP', 'LIMITED_USER_GROUP', 'OTHER_GROUPS'); ``` - Adjust `MGMT_P1` values if throttling occurs. ### Does It Break Existing Functionality? **Unlikely, but caution is needed**: - **Safe Aspects**: - The script checks for existing objects (`PLAN1`, `LIMITED_USER_GROUP`, mappings) to avoid duplicates. - It preserves your plan’s methods (`RATIO`, etc.) and only updates necessary directives. - It adds `USER_A` without altering other users’ mappings unless they’re in the input list. - No database bounce is required; changes are dynamic via `SCOPE=BOTH`. - **Potential Risks**: - **CPU Redistribution**: The new `MGMT_P1` values (70 for `LIMITED_USER_GROUP`, 20 for `LOW_GROUP`, 5 for `HIGH_GROUP`/`OTHER_GROUPS`) could reduce CPU for existing groups, especially `HIGH_GROUP` if it previously had more. - **Plan Activation**: Enabling `PLAN1` overrides any other active plan, which could change resource allocation for all users. - **Session Mapping**: Existing sessions for `USER_A` won’t adopt the new group until reconnected. - **Mitigation Strategy**: - **Pre-Check**: Review current directives and mappings (queries above). - **Test Environment**: Run the script in a non-production RDS instance to assess impact. - **Adjust MGMT_P1**: If `HIGH_GROUP` or `OTHER_GROUPS` need more CPU, modify their `MGMT_P1` in Step 4 (e.g., set `HIGH_GROUP` to 15, `OTHER_GROUPS` to 5, keeping sum <= 100). - **Rollback Plan**: Save the current plan and mappings: ```sql SELECT plan, group_or_subplan, mgmt_p1, utilization_limit FROM dba_rsrc_plan_directives WHERE plan = 'PLAN1'; SELECT attribute, value, consumer_group FROM dba_rsrc_group_mappings; ``` Revert if needed: ```sql BEGIN DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA(); DBMS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP_MAPPING( ATTRIBUTE => DBMS_RESOURCE_MANAGER.ORACLE_USER, VALUE => 'USER_A' ); -- Restore original directives if needed DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE( PLAN => 'PLAN1', GROUP_OR_SUBPLAN => 'LOW_GROUP', NEW_MGMT_P1 => original_value, NEW_UTILIZATION_LIMIT => 30 ); -- Similarly for HIGH_GROUP, OTHER_GROUPS DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA(); DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA(); END; / ``` ### Modified Script (If Needed) If you want to minimize changes to existing directives (e.g., preserve `HIGH_GROUP`’s CPU allocation), modify Step 4 in the script to avoid updating `HIGH_GROUP` unless necessary. Here’s an alternative Step 4 snippet to replace in the original script: ```sql -- Step 4: Configure Directives for PLAN1 DBMS_OUTPUT.PUT_LINE('Step 4: Configuring directives for PLAN1...'); BEGIN -- Directive for LIMITED_USER_GROUP SELECT COUNT(*) INTO l_plan_exists FROM dba_rsrc_plan_directives WHERE plan = 'PLAN1' AND group_or_subplan = 'LIMITED_USER_GROUP'; IF l_plan_exists = 0 THEN DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( PLAN => 'PLAN1', GROUP_OR_SUBPLAN => 'LIMITED_USER_GROUP', COMMENT => 'Limit CPU to ' || l_cpu_limit || '% for users', MGMT_P1 => l_cpu_limit, UTILIZATION_LIMIT => l_cpu_limit, MGMT_MTH => 'RATIO', ACTIVE_SESS_POOL_MTH => 'ACTIVE_SESS_POOL_ABSOLUTE', PARALLEL_DEGREE_LIMIT_MTH => 'PARALLEL_DEGREE_LIMIT_ABSOLUTE', QUEUEING_MTH => 'FIFO_TIMEOUT' ); DBMS_OUTPUT.PUT_LINE('Directive for LIMITED_USER_GROUP created.'); ELSE DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE( PLAN => 'PLAN1', GROUP_OR_SUBPLAN => 'LIMITED_USER_GROUP', NEW_COMMENT => 'Limit CPU to ' || l_cpu_limit || '% for users', NEW_MGMT_P1 => l_cpu_limit, NEW_UTILIZATION_LIMIT => l_cpu_limit ); DBMS_OUTPUT.PUT_LINE('Directive for LIMITED_USER_GROUP updated.'); END IF; -- Adjust LOW_GROUP directive (keep 30% UTILIZATION_LIMIT) SELECT COUNT(*) INTO l_plan_exists FROM dba_rsrc_plan_directives WHERE plan = 'PLAN1' AND group_or_subplan = 'LOW_GROUP'; IF l_plan_exists > 0 THEN DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE( PLAN => 'PLAN1', GROUP_OR_SUBPLAN => 'LOW_GROUP', NEW_COMMENT => 'Limit CPU to 30% for LOW_GROUP', NEW_MGMT_P1 => 20, NEW_UTILIZATION_LIMIT => 30 ); DBMS_OUTPUT.PUT_LINE('Directive for LOW_GROUP updated (MGMT_P1 = 20).'); END IF; -- Ensure OTHER_GROUPS directive exists SELECT COUNT(*) INTO l_plan_exists FROM dba_rsrc_plan_directives WHERE plan = 'PLAN1' AND group_or_subplan = 'OTHER_GROUPS'; IF l_plan_exists = 0 THEN DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( PLAN => 'PLAN1', GROUP_OR_SUBPLAN => 'OTHER_GROUPS', COMMENT => 'Minimal directive for other groups', MGMT_P1 => 10, -- Adjusted to balance with LOW_GROUP MGMT_MTH => 'RATIO', ACTIVE_SESS_POOL_MTH => 'ACTIVE_SESS_POOL_ABSOLUTE', PARALLEL_DEGREE_LIMIT_MTH => 'PARALLEL_DEGREE_LIMIT_ABSOLUTE', QUEUEING_MTH => 'FIFO_TIMEOUT' ); DBMS_OUTPUT.PUT_LINE('Minimal directive for OTHER_GROUPS created.'); ELSE DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE( PLAN => 'PLAN1', GROUP_OR_SUBPLAN => 'OTHER_GROUPS', NEW_COMMENT => 'Minimal directive for other groups', NEW_MGMT_P1 => 10 ); DBMS_OUTPUT.PUT_LINE('Directive for OTHER_GROUPS updated (MGMT_P1 = 10).'); END IF; EXCEPTION WHEN OTHERS THEN l_error_message := 'Error in Step 4 (Configure Directives): ' || SQLERRM; DBMS_OUTPUT.PUT_LINE(l_error_message); RAISE; END; ``` This version avoids modifying `HIGH_GROUP` unless it’s missing a directive, reducing impact on existing users in that group. Adjust `MGMT_P1` for `OTHER_GROUPS` (e.g., 10) based on your needs. ### Testing and Verification - **Pre-Run Check**: Confirm current setup: ```sql SELECT plan, group_or_subplan, mgmt_p1, utilization_limit FROM dba_rsrc_plan_directives WHERE plan = 'PLAN1'; SELECT attribute, value, consumer_group FROM dba_rsrc_group_mappings WHERE value IN ('USER_A', 'USER_B'); ``` - **Post-Run Test**: - Run a CPU-intensive query as `USER_A`: ```sql SELECT /*+ PARALLEL(4) */ COUNT(*) FROM dba_objects, dba_objects; ``` - Verify consumer group: ```sql SELECT sid, username, resource_consumer_group FROM V$SESSION WHERE username = 'USER_A'; ``` - Monitor CPU in CloudWatch and Resource Manager: ```sql SELECT name, active_sessions, cpu_wait_time, consumed_cpu_time FROM V$RSRC_CONSUMER_GROUP WHERE name = 'LIMITED_USER_GROUP'; ``` ### Conclusion The script is designed to minimize disruption by: - Checking existing objects to avoid conflicts. - Preserving your plan’s methods and existing directives where possible. - Using conservative `MGMT_P1` adjustments. - Including robust error handling and validation. However, it could affect existing functionality by redistributing CPU under the `RATIO` method or overriding another active plan. Mitigate this by reviewing current directives, testing in a non-production environment, and keeping a rollback plan. No database bounce is needed, and the script is RDS-compatible with zero-error execution if tested properly.

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)