Purpose
The ALTER SESSION statement is used to modify the properties of the current session.
Note
The ALTER SESSION statement corresponds to multiple functionalities. OceanBase Database currently supports the following four functionalities:
- Switch the current session to a different database.
- Modify the isolation level of the current session.
- Set session variables, supporting the setting of multiple variables in a single statement.
- Enable or disable parallel execution for
QUERYorDMLoperations.
Syntax
ALTER SESSION
{
SET {
CURRENT_SCHEMA = schema_name
| ISOLATION_LEVEL = { READ COMMITTED | SERIALIZABLE }
| variable_name = variable_value[,variable_name = variable_value...]
}
| { ENABLE | DISABLE } PARALLEL { DML | QUERY | DDL }
| FORCE PARALLEL { DML | QUERY | DDL } integer
}
Parameters
| Parameter | Description |
|---|---|
| SET clause | Used to set session-level parameters. |
| CURRENT_SCHEMA = schema_name | Sets the default schema for the current session to schema_name. Subsequent queries without a specified schema will default to this schema. |
| ISOLATION_LEVEL | Sets the transaction isolation level for the current session |
| variable_name = variable_value[,variable_name = variable_value...]; | Specifies the session variables to set, along with their values. When setting multiple variables, they are not separated by commas. |
| * { ENABLE | DISABLE | FORCE } PARALLEL | Enables or disables parallel execution for QUERY or DML operations: * ENABLE: By default, the degree of parallelism (DOP) for the session is determined by the table's DOP value.
ALTER SESSION > Table-level degree of parallelism. |
Examples
Switch the user to
user1mode.-- Create a user obclient> CREATE USER user1 IDENTIFIED BY yourpassword; obclient> ALTER SESSION SET CURRENT_SCHEMA = user1;Modify the session variables
recyclebin,sql_warnings, andtx_isolation.obclient> ALTER SESSION SET recyclebin = 'on' sql_warnings = 'on' tx_isolation = 'read-committed';Enable parallel execution for
QUERYandDMLoperations in the session./* Execute in parallel with a concurrency level of 3 */ obclient> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 3; /* Execute sequentially */ obclient> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 1; /* Enable PDML path and execute in parallel with a concurrency level of 3 */ obclient> ALTER SESSION FORCE PARALLEL DML PARALLEL 3; /* Execute sequentially */ obclient> ALTER SESSION FORCE PARALLEL DML PARALLEL 1;