Oracle Concepts -
Changing Resource Manager Objects
Oracle Tips by Burleson Consulting
Syntax for CREATE_PENDING_AREA Procedure:
This procedure lets you make changes to
resource manager objects.
All changes to the plan schema must be done
within a pending area. The pending area can be thought of as a
"scratch" area for plan schema changes. The administrator creates this
pending area, makes changes as necessary, possibly validates these
changes, and only when the submit is completed do these changes become
You may, at any time while the pending area is
active, view the current plan schema with your changes by selecting
from the appropriate user views.
At any time, you may clear the pending area if
you want to stop the current changes. You may also call the VALIDATE
procedure to confirm whether the changes you has made are valid. You
do not have to do your changes in a given order to maintain a
consistent group of entries. These checks are also implicitly done
when the pending area is submitted.
Note: Oracle allows "orphan" consumer groups
(i.e., consumer groups that have no plan directives that refer to
them). This is in anticipation that an administrator may want to
create a consumer group that is not currently being used, but
will be used in the future. The procedure has no arguments.
Syntax of the VALIDATE_PENDING_AREA Procedure:
The VALIDATE_PENDING_AREA procedure is used to
validate the contents of a pending area before they are submitted. The
procedure has no arguments.
Usage Notes For the Validate and Submit
The following rules must be adhered to, and
they are checked whenever the validate or submit procedures are
No plan schema may contain any loops.
All plans and consumer groups referred to by
plan directives must exist.
All plans must have plan directives that refer
to either plans or consumer groups.
All percentages in any given level must not
add up to greater than 100 for the emphasis resource allocation
No plan may be deleted that is currently being
used as a top plan by an active instance.
For Oracle8i, the plan directive parameter,
parallel_degree_limit_p1, may only appear in plan directives that
refer to consumer groups (i.e., not at subplans).
There cannot be more than 32 plan directives
coming from any given plan (i.e., no plan can have more than 32
There cannot be more than 32 consumer groups
in any active plan schema.
Plans and consumer groups use the same
namespace; therefore, no plan can have the same name as any consumer
There must be a plan directive for
OTHER_GROUPS somewhere in any active plan schema.This ensures that a
session not covered by the currently active plan is allocated
resources as specified by the OTHER_GROUPS directive.
If any of the above rules are broken when
checked by the VALIDATE or SUBMIT procedures, then an informative
error message is returned. You may then make changes to fix the
problem(s) and reissue the validate or submit procedures.
Syntax of the CLEAR_PENDING_AREA Procedure:
The CLEAR_PENDING_AREA procedure clears the
pending area without submitting it, all changes or entries are lost.
The procedure has no arguments.
Syntax of the SUBMIT_PENDING_AREA
The SUBMIT_PENDING_AREA procedure submits the
contents of the pending area. First the contents are validated and
then they are stored as valid in the database. The procedure has no
Syntax of the SET_INITIAL_CONSUMER_GROUP
The SET_INITIAL_CONSUMER_GROUP procedure sets
the initial consumer group to which a user will belong. The user must
have been granted SWITCH_RESOURCE_GROUP permission before you attempt
to run this procedure.
consumer_group IN VARCHAR2);
User ? The user that is to have the resource
Consumer_group ? The resource (or consumer)
group to grant to the user.
Syntax of the
The SWITCH_RESOURCE_GROUP_FOR_SESS procedure
allows an administrator to switch a user's consumer group for the
duration of the current session.
SESSION_ID IN NUMBER,
SESSION_SERIAL IN NUMBER,
CONSUMER_GROUP IN VARCHAR2);
session_id - SID column from the view
session_serial - SERIAL# column from the view V$SESSION
consumer_group - name of the consumer group of which to switch.
Syntax of the
The SWITCH_CONSUMER_GROUP_FOR_USER switches a
user's default consumer group to a new group. This is a permanent
user IN VARCHAR2,
consumer_group IN VARCHAR2);
user - name of the user
consumer_group - name of the consumer group to
This is an excerpt from
the eBook "Oracle
DBA made Simple".
For more details on Oracle
database administration, see the "Easy
Oracle Jumpstart" by Robert Freeman and Steve Karam. It?s
only $19.95 when you buy it directly from the publisher