Script 1: Add User to Existing LOW_GROUP (Minimal Impact, User Gets Shared 30% CPU Limit)
This script adds the user (e.g., USER_A) to the existing LOW_GROUP under PLAN1, sharing the 30% CPU limit with current users (e.g., USER_ABC, USER_123). It makes no changes to plan directives, MGMT_P1 weights, or other groups (HIGH_GROUP, OTHER_GROUPS), ensuring zero impact on existing CPU allocation or functionality. The user will inherit LOW_GROUP's 30% cap and existing MGMT_P1 share (e.g., 20 or whatever is set), split among group members when active.
sql
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% UTILIZATION_LIMIT.
-- 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, 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 (change as needed)
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;
/Script 2: Add User to New LIMITED_USER_GROUP with 70% CPU Limit (Added to PLAN1 with Minimum Changes)
This script adds the user (e.g., USER_A) to a new LIMITED_USER_GROUP under PLAN1 with a 70% CPU limit (UTILIZATION_LIMIT => 70, MGMT_P1 => 70), making minimal changes to existing directives (e.g., slight reduction in MGMT_P1 for LOW_GROUP to 15 to balance the RATIO method, keeping HIGH_GROUP at 10 and OTHER_GROUPS at 5). This adds great benefits (dedicated 70% CPU for the user) while preserving PLAN1's structure and methods, reusing existing functionality without creating a new plan.
sql
SET SERVEROUTPUT ON;
-- Assumptions:
-- 1. Running on Oracle RDS with Enterprise Edition.
-- 2. User has ADMINISTER_RESOURCE_MANAGER privilege.
-- 3. PLAN1 exists; LOW_GROUP has 30% UTILIZATION_LIMIT; HIGH_GROUP and OTHER_GROUPS exist.
-- 4. Adds USER_A to new LIMITED_USER_GROUP with 70% CPU limit under PLAN1.
-- 5. Minimal changes: Sets MGMT_P1 = 70 for LIMITED_USER_GROUP; reduces LOW_GROUP to 15, HIGH_GROUP to 10, OTHER_GROUPS to 5 for RATIO balance.
-- 6. No new plan; reuses PLAN1's RATIO method and functionality.
-- 7. No database bounce required; changes apply dynamically.
DECLARE
l_username CONSTANT VARCHAR2(128) := 'USER_A'; -- User to add (change as needed)
l_group_exists NUMBER;
l_directive_exists NUMBER;
l_mapping_exists NUMBER;
l_error_message VARCHAR2(4000);
l_cpu_limit CONSTANT NUMBER := 70; -- CPU limit for LIMITED_USER_GROUP
BEGIN
-- Step 1: Check if PLAN1 Exists
DBMS_OUTPUT.PUT_LINE('Step 1: Checking if PLAN1 exists...');
BEGIN
SELECT COUNT(*) INTO l_directive_exists
FROM dba_rsrc_plans
WHERE plan = 'PLAN1';
IF l_directive_exists = 0 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_directive_exists
FROM dba_users
WHERE username = UPPER(l_username);
IF l_directive_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: Check/Create LIMITED_USER_GROUP
DBMS_OUTPUT.PUT_LINE('Step 4: Checking if consumer group LIMITED_USER_GROUP exists...');
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 users with 70% CPU limit'
);
DBMS_OUTPUT.PUT_LINE('LIMITED_USER_GROUP created.');
ELSE
DBMS_OUTPUT.PUT_LINE('LIMITED_USER_GROUP already exists.');
END IF;
EXCEPTION
WHEN OTHERS THEN
l_error_message := 'Error in Step 4 (Check/Create LIMITED_USER_GROUP): ' || SQLERRM;
DBMS_OUTPUT.PUT_LINE(l_error_message);
RAISE;
END;
-- Step 5: Add/Update Directive for LIMITED_USER_GROUP (70% Limit)
DBMS_OUTPUT.PUT_LINE('Step 5: Adding/Updating directive for LIMITED_USER_GROUP...');
BEGIN
SELECT COUNT(*) INTO l_directive_exists
FROM dba_rsrc_plan_directives
WHERE plan = 'PLAN1' AND group_or_subplan = 'LIMITED_USER_GROUP';
IF l_directive_exists = 0 THEN
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
PLAN => 'PLAN1',
GROUP_OR_SUBPLAN => 'LIMITED_USER_GROUP',
COMMENT => 'Limit CPU to 70% for LIMITED_USER_GROUP',
MGMT_P1 => 70,
UTILIZATION_LIMIT => 70,
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_MGMT_P1 => 70,
NEW_UTILIZATION_LIMIT => 70
);
DBMS_OUTPUT.PUT_LINE('Directive for LIMITED_USER_GROUP updated.');
END IF;
EXCEPTION
WHEN OTHERS THEN
l_error_message := 'Error in Step 5 (Directive for LIMITED_USER_GROUP): ' || SQLERRM;
DBMS_OUTPUT.PUT_LINE(l_error_message);
RAISE;
END;
-- Step 6: Minimal Adjustments to Existing Directives (Balance RATIO with Minimum Changes)
DBMS_OUTPUT.PUT_LINE('Step 6: Minimal adjustments to existing directives for RATIO balance...');
BEGIN
-- Update LOW_GROUP (reduce MGMT_P1 slightly to 15)
SELECT COUNT(*) INTO l_directive_exists
FROM dba_rsrc_plan_directives
WHERE plan = 'PLAN1' AND group_or_subplan = 'LOW_GROUP';
IF l_directive_exists > 0 THEN
DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
PLAN => 'PLAN1',
GROUP_OR_SUBPLAN => 'LOW_GROUP',
NEW_MGMT_P1 => 15 -- Minimal reduction for balance
);
DBMS_OUTPUT.PUT_LINE('LOW_GROUP MGMT_P1 adjusted to 15.');
END IF;
-- Update HIGH_GROUP (set to 10 if not already low)
SELECT COUNT(*) INTO l_directive_exists
FROM dba_rsrc_plan_directives
WHERE plan = 'PLAN1' AND group_or_subplan = 'HIGH_GROUP';
IF l_directive_exists > 0 THEN
DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
PLAN => 'PLAN1',
GROUP_OR_SUBPLAN => 'HIGH_GROUP',
NEW_MGMT_P1 => 10 -- Minimal adjustment
);
DBMS_OUTPUT.PUT_LINE('HIGH_GROUP MGMT_P1 adjusted to 10.');
END IF;
-- Update OTHER_GROUPS (set to 5 if not already)
SELECT COUNT(*) INTO l_directive_exists
FROM dba_rsrc_plan_directives
WHERE plan = 'PLAN1' AND group_or_subplan = 'OTHER_GROUPS';
IF l_directive_exists > 0 THEN
DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
PLAN => 'PLAN1',
GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
NEW_MGMT_P1 => 5 -- Minimal adjustment
);
DBMS_OUTPUT.PUT_LINE('OTHER_GROUPS MGMT_P1 adjusted to 5.');
END IF;
EXCEPTION
WHEN OTHERS THEN
l_error_message := 'Error in Step 6 (Adjust Directives): ' || SQLERRM;
DBMS_OUTPUT.PUT_LINE(l_error_message);
RAISE;
END;
-- Step 7: Grant Switch Privilege to USER_A
DBMS_OUTPUT.PUT_LINE('Step 7: Granting switch privilege to ' || l_username || ' for LIMITED_USER_GROUP...');
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 7 (Grant Switch Privilege): ' || SQLERRM;
DBMS_OUTPUT.PUT_LINE(l_error_message);
RAISE;
END;
-- Step 8: Map USER_A to LIMITED_USER_GROUP
DBMS_OUTPUT.PUT_LINE('Step 8: 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 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: Verify Configuration
DBMS_OUTPUT.PUT_LINE('Step 11: 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 = '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 11 (Verify Configuration): ' || SQLERRM;
DBMS_OUTPUT.PUT_LINE(l_error_message);
RAISE;
END;
DBMS_OUTPUT.PUT_LINE('Successfully added ' || l_username || ' to LIMITED_USER_GROUP with 70% CPU limit 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;
/
No comments:
Post a Comment