Purpose
You can use this statement to set or modify session information such as the session status.
Note
In OceanBase Database, you can use the ALTER SESSION statement for the following purposes:
- Switch the connected database in the current session.
- Modify the isolation level of the current session.
- Specify session variables. You can specify multiple variables in one statement.
- Set parallel execution for queries or DML statements.
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 session switches. |
| ISOLATION_LEVEL | The isolation level of the session. |
| variable_name = variable_value[,variable_name = variable_value...]; | The name and value of a session variable. When you set two or more variables, separate them with spaces instead of commas (,). |
| { ENABLE | DISABLE | FORCE } PARALLEL | Specifies whether to enable parallel execution for queries or DML statements.
ALTER SESSION statement > DOP at the table level. |
Examples
Switch to the
user1schema.obclient> ALTER SESSION SET CURRENT_SCHEMA = user1; Query OK, 0 rows affectedModify the values of the session variables
recyclebin,sql_warnings, andtx_isolation.obclient> ALTER SESSION SET recyclebin = 'on' sql_warnings = 'on' tx_isolation = 'read-committed'; Query OK, 0 rows affectedSet parallel execution for queries and DML statements in a session.
/*Execute queries in parallel at a DOP 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 queries in sequence.*/ 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 parallel DML (PDML) and execute DML statements in parallel at a DOP 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 queries in sequence.*/ 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
