package provides an interface to switch the database service and consumer group of the
When a connection is established with an Autonomous Database, that session is assigned a consumer group. For example, a session could be created using a connection to the LOW service of an Autonomous Database. You might want to switch the consumer group, for example from LOW to HIGH. The
CS_SESSION package provides an API for switching.
Consumer group affects concurrency and degree of parallelism (DOP). For example, statements on a connection established to the LOW database service run serially. Statements on a connection established to the HIGH database service run in parallel. If you have a workload that requires serial statement processing with switching to a HIGH consumer group for a few statements, the
CS_SESSION package enables you to switch.
- See Database Service Names for Autonomous Data Warehouse for more information.
- See Database Service Names for Autonomous Transaction Processing and Autonomous JSON Database for more information.
This procedure switches the database service and consumer group of the current session.
CS_SESSION.SWITCH_SERVICE(service_name IN varchar2);
Specifies the consumer group to update.
Depending on the workload, valid values are:
When called, the procedure switches the session to the specified service and the related consumer group. If the specified service does not exist in that database, an error message is provided. For example, if you specify 'TP' as the service name on a data warehouse workload, the error indicates it's not a valid service name. No error is reported if the current service and the specified service are identical.
The procedure does not reset session attributes. Anything the user set for their session before calling this procedure will continue as-is. For example, if a session parameter was modified and then later the session switched to a different service, the parameter value will stay the same.
BEGIN CS_SESSION.SWITCH_SERVICE('HIGH'); END; /
Security and Access
ADMIN user is granted
EXECUTE privilege on
GRANT OPTION. The privilege is also granted to
DWROLE without the
Additional Security Considerations
If a user is granted
EXECUTE privileges on this procedure and you do not want that user to switch to a specific service, you can use a
AFTER SET CONTAINER trigger to block the operation. This is achieved by creating an
AFTER SET CONTAINER trigger.
CREATE OR REPLACE TRIGGER SESS_SWITCH AFTER SET CONTAINER ON DATABASE BEGIN IF SYS_CONTEXT('USERENV','SESSION_USER') = 'USER' and SYS_CONTEXT('USERENV','SERVICE_NAME') = 'serviceexample_low.adwc.oraclecloud.com' THEN NULL; ELSE RAISE_APPLICATION_ERROR(-20001, 'Denied! You are not allowed to switch service in the database'); END IF; END; /
The following table describes exceptions for
|20001||Invalid service name. Valid values are HIGH, MEDIUM, LOW.||For a data warehouse workload, a value other than 'HIGH', 'MEDIUM', 'LOW' was specified.|
|20001||Invalid service name. Valid values are HIGH, MEDIUM, LOW, TP, TPURGENT.||For a transaction processing workload, a value other than 'HIGH', 'MEDIUM', 'LOW', 'TP', 'TPURGENT' was specified.|
|20002||Service switch failed.||Failed to switch to the new service.|