System variables in OceanBase Database can be configured to meet business requirements. This topic describes how to view the system variables of a tenant.
View tenant system variables using the SHOW statement
Log in to a user tenant of the cluster using the root user.
For example, for the
mq_t1tenant:obclient -h172.30.xx.xx -P2883 -uroot@mq_t1#cluster -p**** -AExecute the SHOW statement to query the system variables:
SHOW [SESSION | GLOBAL] VARIABLES [LIKE 'pattern' | WHERE expr]where
SESSION | GLOBAL: SESSION indicates session-level system variables and GLOBAL indicates global-level system variables. If not specified, session-level system variables will be displayed by default.pattern: indicates the variable keyword.
Here is an example:
Query the session variables for
ob_query_timeout:obclient [oceanbase]> SHOW VARIABLES LIKE 'ob_query_timeout';or
obclient [oceanbase]> SHOW SESSION VARIABLES LIKE 'ob_query_timeout';The result is as follows:
+------------------+-------------+ | Variable_name | Value | +------------------+-------------+ | ob_query_timeout | 10000000 | +------------------+-------------+ 1 row in setQuery the global variables for
ob_query_timeout:obclient [oceanbase]> SHOW GLOBAL VARIABLES WHERE variable_name LIKE 'ob_query_timeout';The result is as follows:
+------------------+-------------+ | Variable_name | Value | +------------------+-------------+ | ob_query_timeout | 10000000 | +------------------+-------------+ 1 row in set
Query tenant system variables using views
Log in to the
mysql001tenant of the cluster using the root user.obclient -P2881 -h172.30.xx.xx -uroot@mysql001#cluster -p**** -A -DoceanbaseAccess the database named
oceanbase.obclient [(none)]> USE oceanbase;Query the system variables through views.
Query the session variables for
ob_query_timeoutusing theINFORMATION_SCHEMA.SESSION_VARIABLESview.obclient [oceanbase]> SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME = 'ob_query_timeout';The result is as follows:
+------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +------------------+----------------+ | ob_query_timeout | 10000000 | +------------------+----------------+ 1 row in setQuery the global variables for
ob_query_timeoutusing theINFORMATION_SCHEMA.GLOBAL_VARIABLESview.obclient [oceanbase]> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'ob_query_timeout';The result is as follows:
+------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +------------------+----------------+ | ob_query_timeout | 10000000 | +------------------+----------------+ 1 row in set ``` in setQuery the modification status and default value of the
ob_query_timeoutvariable using theDBA_OB_SYS_VARIABLESview.obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_SYS_VARIABLES WHERE NAME='ob_query_timeout';The 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 using thesysuser.obclient -h172.30.xx.xx -P2883 -usys@oracle001#cluster -p**** -AQuery the system variables through views.
Query the session variables for
ob_query_timeoutusing theSYS.TENANT_VIRTUAL_SESSION_VARIABLEview.obclient [SYS]> SELECT * FROM SYS.TENANT_VIRTUAL_SESSION_VARIABLE WHERE VARIABLE_NAME = 'ob_query_timeout';The result is as follows:
+------------------+----------+ | VARIABLE_NAME | VALUE | +------------------+----------+ | ob_query_timeout | 10000000 | +------------------+----------+ 1 row in setQuery the global variables for
ob_query_timeoutusing theSYS.TENANT_VIRTUAL_GLOBAL_VARIABLEview.obclient [SYS]> SELECT * FROM SYS.TENANT_VIRTUAL_GLOBAL_VARIABLE WHERE VARIABLE_NAME = 'ob_query_timeout';The result is as follows:
+------------------+----------+ | VARIABLE_NAME | VALUE | +------------------+----------+ | ob_query_timeout | 10000000 | +------------------+----------+ 1 row in setQuery the modification status and default value of the
ob_query_timeoutvariable using theDBA_OB_SYS_VARIABLESview.obclient [SYS]> SELECT * FROM SYS.DBA_OB_SYS_VARIABLES WHERE NAME='ob_query_timeout';The 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: