Modify system variables of a tenant

Setting system variables can help OceanBase Database behave according to business requirements. This topic describes how to modify system variables of a tenant.

You can use the SET statement to modify system variables of a tenant.

• Setting a session variable takes effect only for the current session, not for other sessions.

• Setting a global variable does not take effect for the current session and requires logging on again to create a new session before taking effect.

  Notice

  Setting a global variable requires the SUPER or ALTER SYSTEM privilege. Ensure that you have the required privileges before setting a global variable.

The system variable ob_query_timeout in the following example is used to set the timeout period for SQL queries in microseconds. When performing big data queries, you can increase the value of ob_query_timeout to avoid query timeout and execution failure.

Modify a session-level system variable

1. Log on to a user tenant of the cluster as the root user.

   The following command uses the mq_t1 tenant as an example:

   obclient -h172.30.xx.xx -P2883 -uroot@mq_t1#cluster -p**** -A
   

2. Query the session-level system variables of the tenant.

   obclient [(none)]> SHOW VARIABLES LIKE 'ob_query_timeout';
   +------------------+----------+
   | Variable_name    | Value    |
   +------------------+----------+
   | ob_query_timeout | 10000000 |
   +------------------+----------+
   1 row in set
   

3. Execute the SET statement to modify the value of a session-level system variable.

   obclient [oceanbase]> SET ob_query_timeout = 20000000;
   Query OK, 0 rows affected
   

   Note

   In Oracle mode, you can modify the value of session-level variables by using ALTER SESSION SET ob_query_timeout = 20000000;.

4. View the modification result.

   obclient [(none)]> SHOW VARIABLES WHERE variable_name LIKE 'ob_query_timeout';
   +------------------+----------+
   | Variable_name    | Value    |
   +------------------+----------+
   | ob_query_timeout | 20000000 |
   +------------------+----------+
   1 row in set
   

Modify a global-level system variable

1. Log on to a user tenant of the cluster as the root user.

   The following command uses the mq_t1 tenant as an example:

   obclient -h172.30.xx.xx -P2883 -uroot@mq_t1#cluster -p**** -A
   

2. Query the global-level system variables of the tenant.

   obclient [(none)]> SHOW GLOBAL VARIABLES WHERE variable_name LIKE  'ob_query_timeout';
   +------------------+----------+
   | Variable_name    | Value    |
   +------------------+----------+
   | ob_query_timeout | 10000000 |
   +------------------+----------+
   1 row in set
   

3. Execute the SET statement to modify the value of a global-level system variable.

   obclient [oceanbase]> SET GLOBAL ob_query_timeout = 20000000;
   Query OK, 0 rows affected
   

4. View the modification result.

   obclient [(none)]> SHOW GLOBAL VARIABLES WHERE variable_name LIKE 'ob_query_timeout';
   +------------------+----------+
   | Variable_name    | Value    |
   +------------------+----------+
   | ob_query_timeout | 20000000 |
   +------------------+----------+
   1 row in set
   

Modify a Boolean variable

Use the SHOW VARIABLES statement to modify the value of a Boolean variable in the following ways:

SET foreign_key_checks = ON;
SET foreign_key_checks = TRUE;
SET foreign_key_checks = 1;

SET GLOBAL foreign_key_checks = OFF;
SET GLOBAL foreign_key_checks = FALSE;
SET GLOBAL foreign_key_checks = 0;

More information

For more information about system variables, visit the following links:

• Overview of system variables
• View system variables of a tenant