The database administrator creates the Consumer Group using the package DBMS_RESOURCE_MANAGER. The fact is that this package contains a number of procedures that we will discuss later. However, you can view all the procedures through...

DESC DBMS_RESOURCE_MANAGER

To create a Consumer Group, it is necessary first to create the Pending Area. If you were to create the Consumer Group directly without creating the Pending Area, the operation would fail.

Steps to create the Consumer Group:

1. Clean the Pending Area.
2. This is done using the procedure CLEAR_PENDING_AREA.

BEGIN

SYS.DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;

COMMIT;

END; 

2. Creating the Pending Area.

This is done using the procedure CREATE_PENDING_AREA.

BEGIN

SYS.DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;

COMMIT;

END; 

3. Creating the Consumer Group.

This is done using the procedure CREATE_CONSUMER_GROUP.

DECLARE

CONSUMER_GROUP VARCHAR2(200);

COMMENT VARCHAR2(200);

BEGIN

CONSUMER_GROUP := 'NEW_GROUP';

COMMENT := 'THIS IS NEW GROUP';

SYS.DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP ( CONSUMER_GROUP, COMMENT);

COMMIT;

END; 

We have created a Consumer Group named NEW_GROUP, and you can now query it to verify the creation process.

SELECT DISTINCT(CONSUMER_GROUP) FROM DBA_RSRC_CONSUMER_GROUPS;

4-Setting variables:

This is done through the procedure SUBMIT_PENDING_AREA.

Where it saves all the changes in the Pending Area and then reverts them to the default state, provided that the changes are correct. If they are incorrect, the Pending Area remains unchanged.

BEGIN

SYS.DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;

COMMIT;

END;