Set variables

You can set system variables to ensure that the behaviors of OceanBase Database meet your business requirements.

Variable categories

The system variables of OceanBase Database can be categorized into global variables and session-level variables.

• A global variable is used to implement a global modification. Different users of the same database tenant share the settings of global variables. Modifications to global variables remain effective after you exit the session. In addition, modifications to a global variable do not take effect on the currently open session and take effect only after a new session is established.

• A session-level variable is used to implement a session modification. When a client is connected to the database, the database copies global variables to automatically generate session-level variables. Modifications made to session-level variables apply to the current session only.

Variables whose names start with an underscore (_) are hidden variables. For example, _primary_zone_entity_count is a hidden variable. Hidden variables are used by developers only for troubleshooting or emergency O&M.

Views related to system variables

Views related to system variables in a MySQL tenant include the following ones:

• INFORMATION_SCHEMA.GLOBAL_VARIABLES: records global variables in the current tenant.

• INFORMATION_SCHEMA.SESSION_VARIABLES: records session-level variables in the current tenant.

• DBA_OB_SYS_VARIABLES: records the modification records and default values of global variables in the current tenant.

Views related to system variables in an Oracle tenant include the following ones:

• SYS.TENANT_VIRTUAL_GLOBAL_VARIABLE: records global variables in the current tenant.

• SYS.TENANT_VIRTUAL_SESSION_VARIABLE: records session-level variables in the current tenant.

• DBA_OB_SYS_VARIABLES: records the modification records and default values of global variables in the current tenant.

For more information about views, see System views in OceanBase Database Reference Guide.

Set variables

The SQL syntax for setting global and session-level variables is as follows:

SET VARIABLE_NAME = 'VALUE'
SET GLOBAL VARIABLE_NAME = 'VALUE'

You can execute SQL statements to set session-level and global variables. Here are some examples:

obclient> SET ob_query_timeout = 20000000;
obclient> SET GLOBAL ob_query_timeout = 20000000;

The values of some global or session-level variables are of the INT type and set to ON/OFF or True/False in the SHOW VARIABLE command. You can set these variables in the following way. Here are some examples:

SET foreign_key_checks = ON;
SET foreign_key_checks = 1;
SET GLOBAL foreign_key_checks = ON;
SET GLOBAL foreign_key_checks = 1;

You can also set global or read-only variables when you create a tenant by using the following syntax:

CREATE TENANT [IF NOT EXISTS] tenant_name 
     [tenant_characteristic_list] [opt_set_sys_var];

tenant_characteristic_list: 
  tenant_characteristic [, tenant_characteristic...]

tenant_characteristic: 
      COMMENT 'string'  
    | {CHARACTER SET | CHARSET} [=] charsetname 
    | COLLATE [=]  collationname 
    | PRIMARY_ZONE [=] zone  
    | RESOURCE_POOL_LIST [=](poolname [, poolname...])
    | LOCALITY [=] 'locality description'

opt_set_sys_var:
  {SET | SET VARIABLES | VARIABLES} system_var_name = expr [,system_var_name = expr] ...

When you create a tenant, set the value of the read-only variable ob_compatibility_mode to mysql or oracle and the value of the global variable ob_tcp_invited_nodes to %. Here is an example:

obclient> CREATE TENANT IF NOT EXISTS test_tenant
charset='utf8mb4', primary_zone='zone1;zone2,zone3', resource_pool_list=('pool1')
SET ob_compatibility_mode='oracle', ob_tcp_invited_nodes='%';

Query a variable

• You can query variables by using the SHOW VARIABLES statement.

  The SQL syntax for querying global and session-level variables is as follows:

  SHOW VARIABLES [LIKE 'pattern' | WHERE expr]
  SHOW GLOBAL VARIABLES [LIKE 'pattern' | WHERE expr]
  

  You can execute SQL statements to query session-level and global variables. Here are some examples:

  obclient> SHOW VARIABLES LIKE 'ob_query_timeout';
  obclient> SHOW VARIABLES WHERE VARIABLE_NAME = 'ob_query_timeout';
  obclient> SHOW GLOBAL VARIABLES LIKE 'ob_query_timeout';
  obclient> SHOW GLOBAL VARIABLES WHERE VARIABLE_NAME = 'ob_query_timeout';
  obclient> SHOW GLOBAL VARIABLES WHERE VARIABLE_NAME LIKE 'ob_query_timeout';
  +------------------+----------+
  | Variable_name    | Value    |
  +------------------+----------+
  | ob_query_timeout | 10000000 |
  +------------------+----------+
  1 row in set (0.00 sec)
  

  The following table describes the column attributes in the execution results.

  Column name	Description
  Variable_name	The name of the variable.
  Value	The value of the variable.

• You can execute the SELECT statement to query session-level and global variables from the related views of the current tenant. The SQL syntax is as follows:

  SELECT * FROM view_name WHERE VARIABLE_NAME = '[var name]'
  

  In Oracle mode, you can execute the SELECT statement to query session-level and global variables from the related views of the current tenant. Here are some examples:

  obclient> SELECT * FROM SYS.TENANT_VIRTUAL_GLOBAL_VARIABLE WHERE VARIABLE_NAME = 'ob_query_timeout';
  
  obclient> SELECT * FROM SYS.TENANT_VIRTUAL_SESSION_VARIABLE WHERE VARIABLE_NAME = 'ob_query_timeout';
  

  In MySQL mode, you can execute the SELECT statement to query session-level and global variables from the related views of the current tenant. Here are some examples:

  obclient> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'ob_query_timeout';
  
  obclient> SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME = 'ob_query_timeout';
  

• You can use the following statements to query the modification records and default values of global variables.

  • MySQL mode

    obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_SYS_VARIABLES WHERE NAME='ob_query_timeout';
    

  • Oracle mode

    obclient[SYS] > SELECT * FROM SYS.DBA_OB_SYS_VARIABLES WHERE NAME='ob_query_timeout';
    

More information

For more information about the variables, see System variables in System Reference.