Oracle Cloud Infrastructure Documentation

CS_SESSION Package

The CS_SESSION package provides an interface to switch the database service and consumer group of the existing session.

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. See Database Service Names for Autonomous Database for more information.

The consumer groups determine 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.

Parent topic: Autonomous Database Supplied Package Reference

SWITCH_SERVICE Procedure

This procedure switches the database service and consumer group of the current session.

Syntax

CS_SESSION.SWITCH_SERVICE(service_name IN varchar2);

Parameters

Parameter Description
service_name

Specifies the consumer group to update.

Depending on the workload, valid values are: HIGH, MEDIUM, LOW, TP, or TPURGENT.

Usage Notes

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.

Example

BEGIN
  CS_SESSION.SWITCH_SERVICE('HIGH');
END;
/

Security and Access

The ADMIN user is granted EXECUTE privilege on CS_SESSION with GRANT OPTION. The privilege is also granted to DWROLE without the GRANT OPTION.

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;
/

Error Messages

The following table describes exceptions for CS_SESSION

Error Message Cause
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.

Parent topic: CS_SESSION Package