ALTER SESSION

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

Examples

• Switch to the user1 schema.

  obclient> ALTER SESSION SET CURRENT_SCHEMA = user1;
  Query OK, 0 rows affected
  

• Modify the values of the session variables recyclebin, sql_warnings, and tx_isolation.

  obclient> ALTER SESSION SET recyclebin = 'on' 
                             sql_warnings = 'on' 
                             tx_isolation = 'read-committed';
  Query OK, 0 rows affected
  

• Set 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