11.2. Working with the Pending Area

Before defining or updating any DRM objects, you must first establish a pending area. A pending area is a staging area where resource management objects can be defined and validated before they are activated. If you forget to create the pending area, you will receive the following error message if you try to create or update a DRM object:

ERROR at line 1:
ORA-29371: pending area is not active
ORA-06512: at "SYS.DBMS_RMIN", line 115
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 108
ORA-06512: at line 1

The next sections explain how to manage pending areas. You will learn to create, validate, submit, and clear them.

11.2.1. Creating a Pending Area

To create a pending area, simply execute the DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA procedure. This procedure accepts no parameters, so it can be called as follows:

SQL> exec dbms_resource_manager.create_pending_area();

PL/SQL procedure successfully completed.

Once a pending area has been created, all changes will automatically be stored there until they are validated and submitted.

11.2.2. Validating Changes

After changes have been made in the pending area, they need to be checked for validity before being activated. This can be accomplished through the DBMS_RESOURCE_GROUP.VALIDATE_PENDING_AREA procedure.

The validation process verifies that any changes in the pending area will not result in a violation of any of the following rules:

• No plan schema can contain a loop.

• All DRM objects identified in a plan directive must exist.

• All plan directives refer to either plans or resource groups.

• Allocation percentages for a given level cannot exceed 100.

• Deletes are not allowed for top plans being used by an active instance.

• Only plan directives that refer to consumer resource groups are allowed to set the following parameters:

  • ACTIVE_SESS_POOL_P1

  • MAX_EST_EXEC_TIME

  • MAX_IDLE_BLOCKER_TIME

  • MAX_IDLE_TIME

  • PARALLEL_DEGREE_LIMIT_P1

  • QUEUEING_P1

  • SWITCH_ESTIMATE

  • SWITCH_GROUP

  • SWITCH_TIME

  • SWITCH_TIME_IN_CALL

  • UNDO_POOL

• An active plan schema can contain no more than 32 resource consumer groups.

• Plan names cannot conflict with resource consumer group names.

• There must be a plan directive for the OTHER_GROUPS group to allocate resources for sessions not identified in the active plan.

If any of the preceding rules are violated, the VALIDATE_PENDING_AREA procedure will return errors for each violation. Here's an example:

SQL> exec dbms_resource_manager.validate_pending_area;
BEGIN dbms_resource_manager.validate_pending_area; END;
*

ERROR at line 1:
ORA-29382: validation of pending area failed
ORA-29377: consumer group OTHER_GROUPS is not part of top-plan OLTP_PLAN
ORA-29383: all leaves of top-plan OLTP_PLAN must be consumer groups
ORA-29374: resource plan OLTP_PLAN in top-plan OLTP_PLAN has no plan directives
ORA-29377: consumer group OTHER_GROUPS is not part of top-plan OFF_HOURS_PLAN
ORA-29383: all leaves of top-plan OFF_HOURS_PLAN must be consumer groups
ORA-29374: resource plan OFF_HOURS_PLAN in top-plan OFF_HOURS_PLAN has no plan
  directives

ORA-29377: consumer group OTHER_GROUPS is not part of top-plan DAY_PLAN
ORA-29383: all leaves of top-plan DAY_PLAN must be consumer groups
ORA-29374: resource plan DAY_PLAN in top-plan DAY_PLAN has no plan directives
ORA-06512: at "SYS.DBMS_RMIN", line 402
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 437
ORA-06512: at line 1

If validation is successful, no error messages will be returned.

11.2.3. Submitting the Pending Area

When you are ready to make your changes active, you can use the DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA procedure, as shown here:

SQL> exec dbms_resource_manager.submit_pending_area;

PL/SQL procedure successfully completed.

As you can see, no parameters are required when submitting the pending area. Submitting the contents of the pending area will activate those objects (move them to the data dictionary). Active objects are stored in the data dictionary and can be enabled by DRM.

Just because a DRM object is active does not mean it is enabled. It simply means it can be enabled or be included in an enabled plan.

Submitting the pending area actually performs three distinct actions: It validates, submits, and clears the pending area. Therefore, it is not required to perform a separate validation before submitting. However, from a debugging standpoint, it is beneficial to validate changes on an incremental basis rather than waiting until submit time.

11.2.4. Clearing the Pending Area

To clear the pending area without submitting your changes, you can use the DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA procedure, as shown here:

SQL> exec dbms_resource_manager.clear_pending_area;

PL/SQL procedure successfully completed.

Clearing the pending area drops everything in the pending area irretrievably, so use this procedure with caution. As mentioned earlier, submitting the pending area also clears the pending area, so it is not necessary to use this procedure after a successful submit is performed.

The name of this procedure is somewhat misleading. It seems to imply that the objects in the pending area will be cleared, but that the pending area will remain intact. This is not true. Once the pending area is cleared, a new pending area must be created before making any new changes.