Purpose
The ALTER SESSION statement is used to modify the properties of the current session.
Note
ALTER SESSION corresponds to multiple statements, and OceanBase Database currently supports the following four types of statements:
- Switch the database to which the current session is connected.
- Modify the isolation level of the current session.
- Set session variables, supporting the setting of multiple variables in a single statement.
- Enable parallel execution for
QUERYorDML.
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 use this schema by default. |
| ISOLATION_LEVEL | Sets the transaction isolation level for the current session |
| variable_name = variable_value[,variable_name = variable_value...]; | Specifies the name and value of the session variable to be set. When setting multiple variables, they are not separated by commas. |
| * { ENABLE | DISABLE | FORCE } PARALLEL | Enables parallel execution for QUERY or DML: * 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 values of 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
QUERYandDMLin 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;