SET_CONSUMER_GROUP_MAPPING

The SET_CONSUMER_GROUP_MAPPING procedure is used to add, modify, or drop the mapping of session and resource group based on the login and runtime session attributes. This enables finer-grained resource management.

Syntax

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
    attribute        IN VARCHAR2,
    value            IN VARCHAR2,
    consumer_group   IN VARCHAR2 DEFAULT NULL
);

Parameters

Considerations

• If no mapping is specified for the attribute and value, a mapping is created for the specified resource group.
• If a mapping is specified for the attribute and value, the resource group of the mapping is updated to the specified one.
• If consumer_group is NULL, all the mappings for the specified attribute and value are dropped.

The following table lists the built-in FUNCTION resource group mapping rules in OceanBase Database. You can use the DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING procedure to modify or drop these mapping rules.

• USER: All sessions of the specified user are mapped to the target resource group.
• COLUMN: You can map the value of a specific table field, for example, t2.c3=3 for user1, to a resource group. This way, you can achieve more granular resource isolation.
• FUNCTION: You can map internal tasks such as major compactions, backups, and statistics collection to a resource group. The following table lists the built-in FUNCTION types.

Examples

1. Map by user

CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
  attribute      => 'USER',
  value          => 'user1',
  consumer_group => 'group1'
);

2. Map by table field values

CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
  attribute      => 'COLUMN',
  value          => 't2.c3 = 3 for user1',
  consumer_group => 'group1'
);

3. Map by internal task types

CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
  attribute      => 'FUNCTION',
  value          => 'COMPACTION_HIGH',
  consumer_group => 'group2'
);