View system variables of a tenant

You can set system variables to ensure that the behaviors of OceanBase Database meet the service 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.

   To log on to the mq_t1 tenant, run the following command:

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

2. Execute the SHOW statement to query the system variables.

   The syntax is as follows:

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

   The parameters are described as follows:

   • SESSION | GLOBAL: SESSION specifies to view session variables and GLOBAL specifies to view global variables. If you do not specify any keyword, session variables are displayed by default.
   • pattern: a variable keyword.

   Here are some examples:

   • To query the session variables of the ob_query_timeout tenant, execute the following statement:

     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
     

   • To query the global variables of the ob_query_timeout tenant, execute the following statement:

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

Query views for 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. Access the database named oceanbase.

     obclient [(none)]> USE oceanbase;
     

  3. Query views for variable values.

     • Query the INFORMATION_SCHEMA.SESSION_VARIABLES view for the values of session variables of the ob_query_timeout tenant.

       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
       

     • Query the INFORMATION_SCHEMA.GLOBAL_VARIABLES view for the values of global variables of the ob_query_timeout tenant.

       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 views for variable values.

     • Query the SYS.TENANT_VIRTUAL_SESSION_VARIABLE view for the values of session variables of the ob_query_timeout tenant.

       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
       

     • Query the SYS.TENANT_VIRTUAL_GLOBAL_VARIABLE view for the values of global variables of the ob_query_timeout tenant.

       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
       

References

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

• Overview
• Modify system variables of a tenant