The system variables of OceanBase Database can be categorized into global variables and session variables.
A global variable is used to implement a global modification. Different users of the same database tenant share the settings of global variables. Modification to global variables remains effective after you exit the session. In addition, modification to a global variable does not take effect on the currently open session and takes effect only after a new session is established.
Session variable: indicates session-level modifications. When a client is connected to the database, the database copies global variables to automatically generate session variables. Modifications made to session variables apply to the current session only.
Comparison with system configuration items
| Property | System configuration items | System variables |
|---|---|---|
| Effective scope | Affects a cluster, zone, server, or tenant. | Affects a tenant globally or at the session level. |
| Effective modes | * Dynamically take effect: The value of edit_level is dynamic_effective. * Effective upon restart: The value of edit_level is static_effective. |
* 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. |
| Modification | * You can execute SQL statements to modify a configuration item. Example: sql obclient> Alter SYSTEM SET schema_history_expire_time='1h' * You can set the startup parameters to modify a configuration item. Example: sql cd /home/admin && ./bin/observer -o "schema_history_expire_time='1h'" |
Modification can only be performed by using SQL statements. Example: * MySQL mode sql obclient> SET ob_query_timeout = 20000000; obclient> SET GLOBAL ob_query_timeout = 20000000; * Oracle mode sql obclient> SET ob_query_timeout = 20000000; obclient> ALTER SYSTEM SET ob_query_timeout = 20000000; |
| Persistence | Configuration items are persisted into internal tables and configuration files, and can be queried from the /home/admin/oceanbase/etc/observer.config.bin and /home/admin/oceanbase/etc/observer.config.bin.history files. |
Only variables at the global level are persisted, while those at the session level are not. |
| Life cycle | Long. A configuration item remains effective for the entire duration of a process. | Short. A variable takes effect only after the tenant schema is created. |
| Querying | You can query a configuration item by using the SHOW PARAMETERS statement. |
You can query a variable by using the SHOW [GLOBAL] VARIABLES statement. |
This chapter introduces all system variables in OceanBase and the corresponding default values, parameter descriptions, value ranges, and usage instructions. For more information about the system variables, see Tenant management variables.