Purpose
The ALTER SESSION statement is used to modify the properties of the current session.
Note
ALTER SESSION corresponds to multiple statements. OceanBase Database currently supports the following four:
- Switch the current session to connect 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 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 will default to this schema if no schema is specified. |
| 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 set. When setting multiple variables, variables are not separated by commas. |
| * { ENABLE | DISABLE | FORCE } PARALLEL | Sets the parallel execution capability for QUERY or DML: * ENABLE: By default, the parallelism value for the session is determined by the table's DOP value.
ALTER SESSION > table-level 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
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;