Manage tenant variables

This topic describes how to query and set tenant variables.

Tenant variables are classified into global variables and session-level variables. By default, session-level variables inherit from global variables. You can set session-level variables after a session is established.

Query variables

You can query variable values by using the SHOW statement or views.

Query variables by using the SHOW statement

The syntax is as follows:

• Query session-level variables

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

• Query global variables

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

Here, pattern represents a variable keyword.

Here is an example:

obclient> SHOW VARIABLES LIKE 'ob_query_timeout';

obclient> SHOW SESSION VARIABLES LIKE 'ob_query_timeout';

obclient> SHOW GLOBAL VARIABLES WHERE variable_name LIKE 'ob_query_timeout';

Obtain variable values by querying views

You can also obtain the values of the global or session-level variables by querying the following view.

• MySQL mode

  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';
  

• Oracle mode

  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';
  

Set variables

A session-level variable takes effect only on the current session. A global variable does not take effect on the current session and takes effect only on sessions established upon re-logon.

To set global variables in MySQL mode, you must have the SUPER or ALTER SYSTEM privilege.

Note

• For more information about how to view privileges in Oracle mode, see View user privileges. If you do not have the required privileges, contact the administrator to obtain the privileges. For more information, see Modify user privileges.
• For more information about how to view your privileges in MySQL mode, see View user privileges. If you do not have the required privileges, contact the administrator to obtain the privileges. For more information, see Modify user privileges.

Syntax for the statement for querying global or session-level variables:

• Set session-level variables

  SET [SESSION] VARIABLE_NAME = 'VALUE';
  

  Note

  In Oracle mode, you can also use the ALTER SESSION SET VARIABLE_NAME = 'VALUE'; statement to set session-level variables.

• Set global variables

  SET GLOBAL VARIABLE_NAME = 'VALUE';
  

  Note

  In Oracle mode, you can also use the ALTER SYSTEM SET VARIABLE_NAME = 'VALUE'; statement to set global variables.

Here is an example:

obclient> SET ob_query_timeout = 20000000;

obclient> SET SESSION ob_query_timeout = 20000000;

obclient> SET GLOBAL ob_query_timeout = 20000000;

For a variable whose variable type is INT and that returns ON/OFF or True/False for the SHOW VARIABLES statement, you can set its value by using one of the following methods:

obclient> SET @@foreign_key_checks = ON;

obclient> SET @@foreign_key_checks = 1;

The preceding two methods are equivalent in setting a session-level variable.

More information

For more information about the variables of OceanBase Database, see Overview of configuration management.

For more information about system variables, see System variables.