Purpose
This statement is used to set or modify session status information.
Note
ALTER SESSION is a statement that supports multiple functions. OceanBase Database currently supports the following four functions:
- Switch the database to which the current session is connected.
- Modify the isolation level of the current session.
- Set session variables. You can set multiple variables in one statement.
- Set the parallel execution capability for
QUERYorDML.
Syntax
ALTER SESSION {
alter_session_set_clause
| { ENABLE | DISABLE | FORCE } PARALLEL { DML | QUERY } [ PARALLEL integer ]
};
alter_session_set_clause:
SET {
CURRENT_SCHEMA = current_schema_name
| ISOLATION_LEVEL = [ READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SERIALIZABLE];
| variable_name = variable_value[,variable_name = variable_value...]
}
Parameters
| Parameter | Description |
|---|---|
| CURRENT_SCHEMA | The name of the schema to which the current session is to be switched. |
| ISOLATION_LEVEL | The isolation level of the session. |
| variable_name = variable_value[,variable_name = variable_value...]; | The name and value of the session variable to be set. When multiple variables are set, they are not separated by commas. |
| * { ENABLE | DISABLE | FORCE } PARALLEL | Set the parallel execution capability for QUERY or DML: * ENABLE: By default, the parallelism of the session is determined by the DOP of the table.
ALTER SESSION > The parallelism at the table level. |
Examples
Switch to the
user1user.obclient> ALTER SESSION SET CURRENT_SCHEMA = user1; Query OK, 0 rows affectedSet the values of the
recyclebin,sql_warnings, andtx_isolationvariables for the current session.obclient> ALTER SESSION SET recyclebin = 'on' sql_warnings = 'on' tx_isolation = 'read-committed'; Query OK, 0 rows affectedSet the parallel execution for
QUERYandDMLstatements in the current session./*Execute in parallel with a concurrency level of 3. */ obclient> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 3; Query OK, 0 rows affected obclient> SELECT * FROM tbl1; +------+------+ | COL1 | COL2 | +------+------+ | 1 | 1 | +------+------+ 1 row in set /*Execute in serial mode. */ obclient> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 1; Query OK, 0 rows affected obclient> SELECT * FROM tbl1; +------+------+ | COL1 | COL2 | +------+------+ | 1 | 1 | +------+------+ 1 row in set /*Enable the PDML path and execute in parallel with a concurrency level of 3. */ obclient> ALTER SESSION FORCE PARALLEL DML PARALLEL 3; Query OK, 0 rows affected obclient> INSERT INTO tbl2 SELECT * FROM tbl1; Query OK, 1 row affected /*Execute in serial mode. */ obclient> ALTER SESSION FORCE PARALLEL DML PARALLEL 1; Query OK, 0 rows affected obclient> INSERT INTO tbl2 SELECT * FROM tbl1; Query OK, 1 row affected