View system variables of a tenant

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

Use the SHOW statement to view system variables of a tenant

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. Execute the SHOW statement to query the system variables.

   Syntax:

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

   Parameters:

   • SESSION | GLOBAL: SESSION indicates session variables and GLOBAL indicates global variables. The default value is SESSION.
   • pattern: keyword of the variable.

   Example:

   • Query the session variables for ob_query_timeout.

     obclient [oceanbase]> SHOW VARIABLES LIKE 'ob_query_timeout';
     +------------------+-------------+
     | Variable_name    | Value       |
     +------------------+-------------+
     | ob_query_timeout | 10000000    |
     +------------------+-------------+
     1 row in set
     
     # or
     
     obclient [oceanbase]> SHOW SESSION VARIABLES LIKE 'ob_query_timeout';
     +------------------+-------------+
     | Variable_name    | Value       |
     +------------------+-------------+
     | ob_query_timeout | 10000000    |
     +------------------+-------------+
     1 row in set
     

   • Query the global variables for ob_query_timeout.

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

Use the views to view system variables of a tenant

• MySQL mode

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

     obclient -P2881 -h172.30.xx.xx -uroot@mysql001#cluster -p**** -A -Doceanbase
     

  2. Enter the oceanbase database.

     obclient [(none)]> USE oceanbase;
     

  3. Query variable values through the views.

     • Use the INFORMATION_SCHEMA.SESSION_VARIABLES view to query the values of session variables for ob_query_timeout.

       obclient [oceanbase]> SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME = 'ob_query_timeout';
       +------------------+----------------+
       | VARIABLE_NAME    | VARIABLE_VALUE |
       +------------------+----------------+
       | ob_query_timeout | 10000000       |
       +------------------+----------------+
       1 row in set
       

     • Use the INFORMATION_SCHEMA.GLOBAL_VARIABLES view to query the values of global variables for ob_query_timeout.

       obclient [oceanbase]> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'ob_query_timeout';
       +------------------+----------------+
       | VARIABLE_NAME    | VARIABLE_VALUE |
       +------------------+----------------+
       | ob_query_timeout | 10000000       |
       +------------------+----------------+
       1 row in set
       

• Oracle mode

  1. Log on to the oracle001 tenant of the cluster as the sys user.

     obclient -h172.30.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. Query variable values through the views.

     • Use the SYS.TENANT_VIRTUAL_SESSION_VARIABLE view to query the values of session variables for ob_query_timeout.

       obclient [SYS]> SELECT * FROM SYS.TENANT_VIRTUAL_SESSION_VARIABLE WHERE VARIABLE_NAME = 'ob_query_timeout';
       +------------------+----------+
       | VARIABLE_NAME    | VALUE    |
       +------------------+----------+
       | ob_query_timeout | 10000000 |
       +------------------+----------+
       1 row in set
       

     • Use the SYS.TENANT_VIRTUAL_GLOBAL_VARIABLE view to query the values of global variables for ob_query_timeout.

       obclient [SYS]> SELECT * FROM SYS.TENANT_VIRTUAL_GLOBAL_VARIABLE WHERE VARIABLE_NAME = 'ob_query_timeout';
       +------------------+----------+
       | VARIABLE_NAME    | VALUE    |
       +------------------+----------+
       | ob_query_timeout | 10000000 |
       +------------------+----------+
       1 row in set
       

More information

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

• Overview of system variables
• Modify system variables of a tenant