Setting up Consumer Groups and Categories with PL/SQL in the Database

How to set up Consumer Groups and Categories with PL/SQL in the Database

BEGIN

  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

  DBMS_RESOURCE_MANAGER.CREATE_CATEGORY(
     CATEGORY => 'dss',
     COMMENT => 'DSS consumer groups');

  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
     CONSUMER_GROUP => 'critical_dss',
     CATEGORY => 'dss',
     COMMENT => 'performance-critical DSS queries');

  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
     CONSUMER_GROUP => 'normal_dss',
     CATEGORY => 'dss',
     COMMENT => 'non performance-critical DSS queries');

  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
     CONSUMER_GROUP => 'etl',
     CATEGORY => 'maintenance',
     COMMENT => 'data import operations');

  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

In addition to the consumer groups that you set up, the database contains predefined consumer groups. The DBA_RSRC_CONSUMER_GROUPS view displays information about consumer groups, and the DBA_RSRC_CATEGORIES view displays information about categories in the database.

Consumer Groups and Categories in an Oracle Database

SQL> SELECT consumer_group, category FROM DBA_RSRC_CONSUMER_GROUPS where
     consumer_group not like 'ORA%' ORDER BY category;

CONSUMER_GROUP                                   CATEGORY
---------------------------------------------  ---------------------------------------------
SYS_GROUP                                                   ADMINISTRATIVE
ETL_GROUP                                                   BATCH
BATCH_GROUP                                             BATCH
DSS_GROUP                                                   BATCH
CRITICAL_DSS                                              DSS
NORMAL_DSS                                               DSS
DSS_CRITICAL_GROUP                              INTERACTIVE
INTERACTIVE_GROUP                               INTERACTIVE
ETL                                                                  MAINTENANCE
LOW_GROUP                                                OTHER
OTHER_GROUPS                                          OTHER
AUTO_TASK_CONSUMER_GROUP          OTHER
DEFAULT_CONSUMER_GROUP               OTHER

13 rows selected