Objective: Write a PL/SQL block that creates, validates, and submits a single-level PDB resource plan with consumer groups and shares-based directives.
A pet-supply application has browsing, shopping-cart, and checkout workloads. Design EPETS_PLAN to favor checkout when these workloads compete for CPU. Use the policy below to create the plan definitions.
This is a written exercise. Enter your proposed code and explanation, then compare them with the reference solution. The website does not execute SQL, connect to Oracle, or automatically grade your answer.
| Consumer group | Workload | SHARES | DOP limit |
|---|---|---|---|
| BUYER | Checkout and purchases | 5 | 1 |
| SHOPPER | Shopping-cart work | 3 | 1 |
| LOOKER | Browsing and general information | 1 | 4 |
| OTHER_GROUPS | Fallback coverage | 1 | 1 |
SHARES are relative weights, not CPU caps or reserved percentages of the physical server. The DOP limit governs parallelism per operation, not the number of connected sessions. These values are teaching choices.
Assume a self-managed Oracle 26ai test PDB, an administrator with ADMINISTER_RESOURCE_MANAGER, unused names for EPETS_PLAN, BUYER, SHOPPER, and LOOKER, and no other pending Resource Manager changes. OTHER_GROUPS is built in. If you later run the example in a lab, first verify the container and inspect existing definitions; do not delete them or clear another administrator's pending work.
shares and parallel_degree_limit_p1. Do not add CPU levels, utilization ceilings, active-call limits, or subplans.For this exercise, write the definition block only. Mapping users, granting group-switch privileges, and activating the plan are follow-up steps to describe, not commands to include.
Use CREATE_PENDING_AREA, CREATE_PLAN, CREATE_CONSUMER_GROUP, CREATE_PLAN_DIRECTIVE, VALIDATE_PENDING_AREA, and SUBMIT_PENDING_AREA from DBMS_RESOURCE_MANAGER. Review the Resource Manager implementation lesson for the sequence.