CREATING DATABASE RESOURCE MANAGER OBJECTS :
Point 1:
We create database resource manager objects using a package called dbms_resource_manager.
Point 2 :
Suppose there are many users requesting for the resources,then it is the DBA’s job to decide like to which user has to be given priority so that he can be associated to the consumer group and hence the response time in retrieving data from the table will be less. so here ,
Create a
(a) Pending Area
(b) Resource consumer groups
(c) Resource plan
(d) Resource plan directive
(e) Validating&Submiting pending area
(f) Associate user with consumer group (along with switch privs)
STEPS :
Step 1 : Select a user.let us consider the user as ‘HR’.
Step 2 : Check its default consumer group using the following query.
SQL> select initial_rsrc_consumer_group from dba_users where username=’HR’;
INITIAL_RSRC_CONSUMER_GROUP
——————————
DEFAULT_CONSUMER_GROUP
Step 3 : Now,create a pending area.
SQL> exec dbms_resource_manager.create_pending_area();
PL/SQL procedure successfully completed.
Step 4: Create a consumer group.
SQL> exec dbms_resource_manager.create_consumer_group(-
> consumer_group => ‘anu’ , comment => ‘Doubt’);
PL/SQL procedure successfully completed.
/* here consumer group name is “anu” & in place of comment you
can write any description about the group for further reference.*/
Step 5 : Then next is we need to create a resource plan.
SQL> exec dbms_resource_manager.create_plan(-
> plan => ‘abc’ ,comment => ‘xyz’);
PL/SQL procedure successfully completed.
Step 6 : Next, create a resource plan directive.
SQL> exec dbms_resource_manager.create_plan_directive (-
> plan => ‘abc’, group_or_subplan => ‘OTHER_GROUPS’ , comment => ‘efg’);
PL/SQL procedure successfully completed.
Step 7 : Validate the pending area.
SQL> exec dbms_resource_manager.validate_pending_area();
PL/SQL procedure successfully completed.
NOTE:
/* here one point u need to notice:if u specify group_or_subplan =DEFAULT_CONSUMER_GROUP’ & u tried to validate the pending area,then u will encounter the following error as follows.
SQL> exec dbms_resource_manager.create_plan_directive (-
> plan => ‘abc’, group_or_subplan => ‘DEFAULT_CONSUMER_GROUP’ , comment => ‘efg’);
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 ABC
ORA-06512: at “SYS.DBMS_RMIN”, line 56
ORA-06512: at “SYS.DBMS_RESOURCE_MANAGER”, line 293
ORA-06512: at line 1 */
Step 8 : Now, after validating we need to submit the pending area.Actually without performing validation u can directly execute submit command as shown below but oracle advices to do validation.
SQL> exec dbms_resource_manager.submit_pending_area();
PL/SQL procedure successfully completed.
Step 9 : Now u need to associate user ‘HR’ with the consumer group ‘anu’ which we created above.
SQL> exec dbms_resource_manager_privs.grant_switch_consumer_group(-
> grantee_name => ‘HR’, consumer_group => ‘Anu’,grant_option => False);
PL/SQL procedure successfully completed.
Note :
/* Here also u need to note one important point.Inorder to make HR user to associate with the cosumer group u need to grant privilege to switch to the consumer group(Anu).otherwise it will throw error as shown below.
SQL> exec dbms_resource_manager.set_initial_consumer_group(-
> user => ‘HR’ , consumer_group => ‘Anu’);
BEGIN dbms_resource_manager.set_initial_consumer_group( user => ‘HR’ , consumer_group => ‘Anu’); END;
*
ERROR at line 1:
ORA-29399: user HR does not have privilege to switch to consumer group ANU
ORA-06512: at “SYS.DBMS_RMIN”, line 56
ORA-06512: at “SYS.DBMS_RESOURCE_MANAGER”, line 315
ORA-06512: at line 1 */
Step 10 : Now,in the last step set the initial consumer group for the user as follows.
SQL> exec dbms_resource_manager.set_initial_consumer_group(-
> user => ‘HR’ , consumer_group => ‘Anu’);
PL/SQL procedure successfully completed.
