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

Query variables by using the SHOW statement

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

Syntax for the SHOW statement:

• Query session-level variables

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

• Query global variables

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

pattern represents a variable keyword.

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

In the sys tenant, you can query the system variables of all tenants in the oceanbase.CDB_OB_SYS_VARIABLES view.

Sample code:

obclient> SELECT * FROM oceanbase.CDB_OB_SYS_VARIABLES WHERE VARIABLE_NAME = 'ob_query_timeout';

In a MySQL tenant, you can also obtain the values of global or session-level variables by querying the following views:

Sample code:

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

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.

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

• Set session-level variables

  SET [SESSION] VARIABLE_NAME = 'VALUE';
  

  Note

  You can also use the ALTER SYSTEM SESSION SET VARIABLE_NAME = 'VALUE'; statement to set session-level variables in Oracle mode.

• Set global variables

  SET GLOBAL VARIABLE_NAME = 'VALUE';
  

  Note

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

  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.

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.

For more information about variables, see System variables.