(Updated 12.11.2019) I am grateful to Mikhail Velikikh for his comment. It depends on which version of Oracle's documentation for 11.2 you read. Pattern matching does work in 11.2.0.4 for the attributes listed in the 12.1 documentation. My testing indicates that pattern matching does not happen in 11.2.0.3.
You cannot pattern match the SERVICE_NAME in 11.2. The attribute value is validated against the list of valid services.
exec DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(DBMS_RESOURCE_MANAGER.SERVICE_NAME, 'WIBBLE', 'MYGROUP1');
ORA-00042: Unknown Service name WIBBLE
However, you can pattern match SERVICE_NAME in 12.1, although SERVICE_NAME is not in the list of attributes for which the documentation says pattern matching is available. This may be a documentation bug (see Oracle support note 1992704.1).The parameters to procedure DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING are:
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
attribute IN VARCHAR2,
value IN VARCHAR2,
consumer_group IN VARCHAR2 DEFAULT NULL);
11.2 Documentation (c)2010
You use the SET_CONSUMER_GROUP_MAPPING procedure to map a session attribute/value pair to a consumer group. The parameters for this procedure are the following:
Parameter
|
Description
|
---|---|
attribute | The session attribute type, specified as a package constant |
value | The value of the attribute |
consumer group | The consumer group to map to for this attribute/value pair |
11.2 Documentation (c)2015
This procedure adds, deletes, or modifies entries that map sessions to consumer groups, based on the session's login and runtime attributes.
Parameter
|
Description
|
---|---|
attribute | Mapping attribute to add or modify. It can be one of the Constants listed. |
value | Attribute value to match. This includes both absolute mapping and regular expressions. |
consumer group | Name of the mapped consumer group, or NULL to delete a mapping |
Usage Notes
If no mapping exists for the given attribute and value, a mapping to the given consumer group will be created. If a mapping already exists for the given attribute and value, the mapped consumer group will be updated to the one given. If the consumer_group argument is NULL, then any mapping from the given attribute and value will be deleted.
The subprogram supports simple regex expressions for the value parameter. It implements the same semantics as the SQL 'LIKE' operator. Specifically, it uses '%' as a multi-character wildcard and '_' as a single character wildcard. The '\' character can be used to escape the wildcards. Note that wildcards can only be used if the attribute is one of the following:
- CLIENT_OS_USER
- CLIENT_PROGRAM*
- CLIENT_MACHINE
- MODULE_NAME
- MODULE_NAME_ACTION
- SERVICE_MODULE
- SERVICE_MODULE_ACTION
Autonomous Database Example
The mappings are reported in the view DBA_RSRC_GROUP_MAPPINGS. An example of using a LIKE expression in a mapping can be found in the standard configuration of autonomous cloud databases. There are 5 standard mappings for 5 services to 5 consumer groups. The automatically created service names are prefixed with the database name, then HIGH, LOW, MEDIUM, TP or TPURGENT., and are suffixed .atp.oraclecloud.com.SELECT * FROM v$services ORDER BY 1;
NAME
----------------------------------------------------------------
KZPKSBZK3RK4G1X_GOFASTER1A_high.atp.oraclecloud.com
KZPKSBZK3RK4G1X_GOFASTER1A_low.atp.oraclecloud.com
KZPKSBZK3RK4G1X_GOFASTER1A_medium.atp.oraclecloud.com
KZPKSBZK3RK4G1X_GOFASTER1A_tp.atp.oraclecloud.com
KZPKSBZK3RK4G1X_GOFASTER1A_tpurgent.atp.oraclecloud.com
kzpksbzk3rk4g1x_gofaster1a
However, the mappings are defined using pattern matching rather than referencing the explicit name of the service, thus exactly the same mappings can be defined on every autonomous database.
select * from DBA_RSRC_GROUP_MAPPINGS order by 1,2;
Consumer
ATTRIBUTE VALUE Group STATUS
------------ --------------- ---------- ----------
ORACLE_USER GGADMIN LOW
SERVICE_NAME %\_HIGH.% HIGH
SERVICE_NAME %\_LOW.% LOW
SERVICE_NAME %\_MEDIUM.% MEDIUM
SERVICE_NAME %\_TP.% TP
SERVICE_NAME %\_TPURGENT.% TPURGENT
Note:- By default, the ORACLE_USER attribute's priority 6 takes precedence over SERVICE_NAME whose priority is 7. Therefore, GGADMIN, the GoldenGate user, always uses the LOW consumer group irrespective of the service that it connects to. The default priorities can be changed.
- Wildcards cannot be used for the ORACLE_USER attribute.