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.
- SWITCH_SERVICE Procedure
This procedure switches the database service and consumer group of the current session.
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: |
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