Purpose
The ALTER SESSION statement is used to modify the properties of the current session.
Note
ALTER SESSION is a statement that supports multiple features. OceanBase Database supports the following four features:
- Switches the database that the current session is connected to.
- Modifies the isolation level of the current session.
- Modifies session variables. Multiple variables can be modified in one statement.
- Enables or disables the parallel execution of `QUERY` or `DML`.
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 | The clause for setting session-level parameters. |
| CURRENT_SCHEMA = schema_name | Sets the default schema of the current session to schema_name. If no schema is specified in subsequent queries, this schema is used by default. |
| ISOLATION_LEVEL | The transaction isolation level of the current 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, no comma is placed between variables. |
| * { ENABLE | DISABLE | FORCE } PARALLEL | The clause for enabling, disabling, or forcing the parallel execution of QUERY or DML: * ENABLE: By default, the degree of parallelism (DOP) for a session is determined by the DOP of the table.
ALTER SESSION > table-level DOP. |
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
recyclebin,sql_warnings, andtx_isolationvariables.obclient> ALTER SESSION SET recyclebin = 'on' sql_warnings = 'on' tx_isolation = 'read-committed';Enable or disable the parallel execution of
QUERYandDMLon the current session./* Enable parallel execution with a concurrency of 3. */ obclient> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 3; /* Enable serial execution. */ obclient> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 1; /* Enable the PDML path and enable parallel execution with a concurrency of 3. */ obclient> ALTER SESSION FORCE PARALLEL DML PARALLEL 3; /* Enable serial execution. */ obclient> ALTER SESSION FORCE PARALLEL DML PARALLEL 1;