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.
View system variables of a tenant by using the SHOW statement
Log in to a user tenant of the cluster as the
rootuser.For example, run the following command to log in to the
mq_t1tenant:obclient -h172.30.xx.xx -P2883 -uroot@mq_t1#cluster -p**** -AExecute the
SHOWstatement to query the system variables of the tenant.The syntax is as follows:
SHOW [SESSION | GLOBAL] VARIABLES [LIKE 'pattern' | WHERE expr]where:
SESSION | GLOBAL:SESSIONspecifies to view session variables andGLOBALspecifies 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:
Query the value of the session-level variable
ob_query_timeout.obclient [oceanbase]> SHOW VARIABLES LIKE 'ob_query_timeout';or
obclient [oceanbase]> SHOW SESSION VARIABLES LIKE 'ob_query_timeout';The query result is as follows:
+------------------+-------------+ | Variable_name | Value | +------------------+-------------+ | ob_query_timeout | 10000000 | +------------------+-------------+ 1 row in setQuery the value of the global variable
ob_query_timeout.obclient [oceanbase]> SHOW GLOBAL VARIABLES WHERE variable_name LIKE 'ob_query_timeout';The query result is as follows:
+------------------+-------------+ | Variable_name | Value | +------------------+-------------+ | ob_query_timeout | 10000000 | +------------------+-------------+ 1 row in set
View system variables of a tenant by querying views
Log in to the
mysql001tenant of the cluster as therootuser.obclient -P2881 -h172.30.xx.xx -uroot@mysql001#cluster -p**** -A -DoceanbaseAccess the database named
oceanbase.obclient [(none)]> USE oceanbase;Query views for variable values.
Query the
INFORMATION_SCHEMA.SESSION_VARIABLESview for the value of the session-level variableob_query_timeout.obclient [oceanbase]> SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME = 'ob_query_timeout';The query result is as follows:
+------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +------------------+----------------+ | ob_query_timeout | 10000000 | +------------------+----------------+ 1 row in setQuery the
INFORMATION_SCHEMA.GLOBAL_VARIABLESview for the value of the global variableob_query_timeout.obclient [oceanbase]> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'ob_query_timeout';The query result is as follows:
+------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +------------------+----------------+ | ob_query_timeout | 10000000 | +------------------+----------------+ 1 row in setQuery the
DBA_OB_SYS_VARIABLESview for the modification status and default value of theob_query_timeoutvariable.obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_SYS_VARIABLES WHERE NAME='ob_query_timeout';The query result is as follows:
+----------------------------+----------------------------+------------------+----------+-----------+-----------+------------------+----------------------------------+---------------+-----------+ | CREATE_TIME | MODIFY_TIME | NAME | VALUE | MIN_VALUE | MAX_VALUE | SCOPE | INFO | DEFAULT_VALUE | ISDEFAULT | +----------------------------+----------------------------+------------------+----------+-----------+-----------+------------------+----------------------------------+---------------+-----------+ | 2023-12-08 14:00:20.583506 | 2023-12-08 14:00:20.583506 | ob_query_timeout | 10000000 | | | GLOBAL | SESSION | Query timeout in microsecond(us) | 10000000 | YES | +----------------------------+----------------------------+------------------+----------+-----------+-----------+------------------+----------------------------------+---------------+-----------+ 1 row in set
Log in to the
oracle001tenant of the cluster as thesysuser.obclient -h172.30.xx.xx -P2883 -usys@oracle001#cluster -p**** -AQuery views for variable values.
Query the
SYS.TENANT_VIRTUAL_SESSION_VARIABLEview for the value of the session-level variableob_query_timeout.obclient [SYS]> SELECT * FROM SYS.TENANT_VIRTUAL_SESSION_VARIABLE WHERE VARIABLE_NAME = 'ob_query_timeout';The query result is as follows:
+------------------+----------+ | VARIABLE_NAME | VALUE | +------------------+----------+ | ob_query_timeout | 10000000 | +------------------+----------+ 1 row in setQuery the
SYS.TENANT_VIRTUAL_GLOBAL_VARIABLEview for the value of the global variableob_query_timeout.obclient [SYS]> SELECT * FROM SYS.TENANT_VIRTUAL_GLOBAL_VARIABLE WHERE VARIABLE_NAME = 'ob_query_timeout';The query result is as follows:
+------------------+----------+ | VARIABLE_NAME | VALUE | +------------------+----------+ | ob_query_timeout | 10000000 | +------------------+----------+ 1 row in setQuery the
DBA_OB_SYS_VARIABLESview for the modification status and default value of theob_query_timeoutvariable.obclient [SYS]> SELECT * FROM SYS.DBA_OB_SYS_VARIABLES WHERE NAME='ob_query_timeout';The query result is as follows:
+------------------------------+------------------------------+------------------+----------+-----------+-----------+------------------+----------------------------------+---------------+-----------+ | CREATE_TIME | MODIFY_TIME | NAME | VALUE | MIN_VALUE | MAX_VALUE | SCOPE | INFO | DEFAULT_VALUE | ISDEFAULT | +------------------------------+------------------------------+------------------+----------+-----------+-----------+------------------+----------------------------------+---------------+-----------+ | 08-DEC-23 02.01.39.236966 PM | 08-DEC-23 02.01.39.236966 PM | ob_query_timeout | 10000000 | NULL | NULL | GLOBAL | SESSION | Query timeout in microsecond(us) | 10000000 | YES | +------------------------------+------------------------------+------------------+----------+-----------+-----------+------------------+----------------------------------+---------------+-----------+ 1 row in set
References
For more information about system variables, see the following topics: