OceanBase Database

SQL - V4.3.0

Download PDF

Overview of system variables

Last Updated:2026-09-03 07:23:56

This topic describes the system variables of OceanBase Database based on the functions of the system variables.

Global level

Variable	Description
validate_password_check_user_name	Specifies whether to allow a user password to be the same as the username.
validate_password_length	Specifies the minimum length of a user password.
validate_password_mixed_case_count	Specifies the minimum number of uppercase and lowercase letters in a user password.
validate_password_number_count	Specifies the minimum number of digits in a user password.
validate_password_policy	Specifies the password check strategy.
validate_password_special_char_count	Specifies the minimum number of special characters in a user password.
ob_tcp_invited_nodes	Specifies the IP address list of the tenant, which is used as the IP address whitelist. The IP address list can contain the following characters: the percent sign (%), the hyphen (-), and the IP address. Multiple IP addresses are separated with commas (,). The IP address list supports IP address list matching, mask matching, and fuzzy matching.
default_password_lifetime	Specifies the password expiration period.
block_encryption_mode	Specifies the encryption algorithm used in the aes_encrypt and aes_decrypt functions.
sql_safe_updates	Specifies whether to enable the SQL safe mode of MySQL. The default value is OFF.

Variable name	Description
version_comment	Displays the version of the OBServer node.
version	Displays the server version number.
ob_last_schema_version	Specifies the latest schema version used in the current session.
ob_proxy_global_variables_version	Specifies the latest version of global variables that the OBServer node returns to the OBProxy.
ob_enable_transmission_checksum	Specifies whether to perform data packet checksum verification between the OBProxy and the OBServer node. This setting takes effect immediately after it is applied.

Variable name	Description
debug_sync	Specifies the synchronization point for debugging and is compatible with MySQL.
ob_global_debug_sync	Specifies the synchronization point for debugging. Unlike `debug_sync`, setting this variable affects all sessions.

Variable name	Description
lower_case_table_names	Specifies whether to make the database case-insensitive.

Variable	Description
query_cache_size	The size of memory allocated for storing the results of queries, which is the unused memory.
query_cache_type	The type of the query cache.
ob_query_timeout	The timeout period for a query.
is_result_accurate	When a query contains the `topk` hint, the `is_result_accurate` variable indicates whether the result is accurate.
net_buffer_length	The maximum size of an SQL query that an OBServer node can accept.
read_only	Specifies whether the tenant is in read-only mode.
ob_read_consistency	The read consistency level.
ob_max_read_stale_time	The maximum latency threshold (in microseconds) for weak-consistency reads.
net_read_timeout	The number of seconds that an OBServer node waits for other data to be read before interrupting the read.
net_write_timeout	The number of seconds that an OBServer node waits for block writes before interrupting the write.
ob_enable_index_direct_select	Specifies whether to allow users to directly query index tables.
ob_enable_aggregation_pushdown	Specifies whether to allow aggregation operations to be pushed down.
ob_enable_jit	Specifies the JIT execution engine mode.
sql_throttle_network	The request queuing time threshold. If the request queuing time is greater than or equal to this value, queries are not executed.
parallel_servers_target	The conditions for queuing a large query on each server.
cte_max_recursion_depth	The maximum recursion depth of a common table expression (CTE). The server terminates any operation that exceeds this CTE value.
regexp_stack_limit	The maximum available memory for the internal stack of a regular expression matching operation.
regexp_time_limit	The time limit for a regular expression matching operation.

Event Scheduler

Variable	Description
event_scheduler	Specifies whether to enable the event scheduler.

Foreign keys

Variable	Description
foreign_key_checks	Specifies whether to check foreign key constraints during DML or DDL operations.

Replicas

Variable	Description
ob_create_table_strict_mode	Specifies whether to use strict mode when creating a table.
ob_default_replica_num	Specifies the default number of replicas for a table in a cluster.

Recycle bin

Variable	Description
recyclebin	Specifies whether to enable the recycle bin.
ob_enable_truncate_flashback	Specifies whether to enable flashback for table truncation.

Caching

Variable	Description
ob_enable_plan_cache	Specifies whether to enable plan cache.
ob_plan_cache_percentage	Specifies the percentage of memory that can be used by the plan cache in a tenant.
ob_plan_cache_evict_high_percentage	Specifies the percentage of memory that triggers the eviction of the plan cache.
ob_plan_cache_evict_low_percentage	Specifies the percentage of memory at which the plan cache eviction is stopped.
ob_bnl_join_cache_size	Specifies the amount of data cached in a batch for a Batch Nest Loop Join operation.

Functions

Variable	Description
interactive_timeout	Specifies the number of seconds the server waits for activity before closing an interactive connection.
wait_timeout	Specifies the number of seconds the server waits for activity before closing a non-interactive connection.
group_concat_max_len	Specifies the maximum length of the result of the `GROUP_CONCAT()` function.

Cluster

Variable	Description
ob_capability_flag	The capabilities of the OBServer node, which are used to negotiate capabilities with the Proxy. This variable informs the Proxy of the features supported and not supported by the current OBServer node.
ob_compatibility_mode	Specifies the compatibility mode of the tenant.
ob_route_policy	Specifies the routing strategy for selecting data replicas on OBServer nodes.
performance_schema	Specifies whether to support performance information queries. The default value is `OFF`. Note This variable is only for compatibility with MySQL 8.0. The related feature is not supported. You can query and set this variable, but the setting does not take effect and no error is returned.
resource_manager_plan	Activates a resource management plan. Different plans use different CPU quotas to limit user activities in a resource group.
license	Specifies the license type.
ob_proxy_partition_hit	Specifies whether to send SQL statement requests to the OBServer node that hosts the primary partition of the data.
ob_org_cluster_id	Specifies the `CLUSTER_ID` of the OceanBase cluster.
server_uuid	Specifies the UUID of the OBServer node.
default_storage_engine	Specifies the default storage engine of the OBServer node.

Directories

Variable	Description
datadir	The local disk path where data is stored.
plugin_dir	The path where the plugin DLL is stored.
secure_file_priv	The path that allows access during import or export to files.
ob_enable_hash_group_by	Specifies whether to enable Hash Group by.

Memory

Variable	Description
ob_sql_work_area_percentage	The percentage of tenant memory allowed for SQL execution.
ob_sql_audit_percentage	The maximum percentage of current tenant memory that can be used for SQL audit.
ob_reserved_meta_memory_percentage	The percentage of tenant memory reserved for storing meta-related structures.
ob_temp_tablespace_size_percentage	The percentage of tenant disk space for temporary tables.

System logs

Variable	Description
binlog_row_image	Specifies whether to record full-column logs.
ob_enable_show_trace	Specifies whether to use trace logs.
ob_log_level	The log level for the session. If not specified, the system log level is used.
tracefile_identifier	Specifies the content to add to the log for easier filtering of row iteration trace logs.

Variable	Description
runtime_filter_type	Specifies the type of runtime filter for the current tenant.
runtime_filter_wait_time_ms	Specifies the maximum wait time for a runtime filter.
runtime_filter_max_in_num	Specifies the number of distinct values (NDV) in the runtime filter.
runtime_bloom_filter_max_size	Specifies the maximum memory size for a runtime Bloom filter, in bytes.

Variable	Description
time_zone	Specifies the time zone for the current tenant session. Valid values: `'+08:00'` (offset) and `Asia/Shanghai` (region).
timestamp	A timestamp in seconds. The value of this variable affects the result of `select now(6)`.
system_time_zone	Specifies the system time zone of the server.
error_on_overlap_time	Specifies whether to return an error when a time zone overlap occurs.
nls_language	Specifies the default language for database internationalization. This variable affects the default values of prompts, dates, month names, `NLS_SORT`, and `NLS_DATE_LANGUAGE`.
nls_territory	Specifies the current region for database internationalization. This variable is used in combination with `nls_language`.
nls_date_language	Specifies the default language for dates in database internationalization.
nls_calendar	Specifies the calendar system used in the database.
ob_timestamp_service	Specifies the timestamp service to be used.

Variable name	Description
autocommit	Specifies whether to automatically commit transactions.
tx_isolation	Specifies the transaction isolation level.
ob_proxy_set_trx_executed	Specifies whether to send the `START TRANSACTION Syntax` statement to the OBServer node through obproxy.
ob_early_lock_release	Specifies whether to enable the early lock release (ELR) feature for transactions. Note This variable is deprecated starting from V2.2.30. The ELR feature is now controlled by the tenant-level configuration item `enable_early_lock_release`.
ob_trx_idle_timeout	Specifies the transaction idle timeout period. A transaction times out if the execution interval between two statements exceeds this period.
transaction_isolation	Specifies the transaction isolation level.
transaction_read_only	Specifies whether to allow only read-only transactions.
ob_trx_lock_timeout	Specifies the timeout period for waiting for a lock.
tx_read_only	Specifies whether the transaction is a read-only transaction.
ob_trx_timeout	Specifies the transaction timeout period.
ob_trace_info	Specifies whether to pass trace information, which can be persisted to the redo log.

Variable name	Description
ob_enable_sql_audit	Specifies whether to enable the SQL audit feature for the current tenant.

Data types

Variable name	Description
nls_length_semantics	Specifies the length semantics for CHAR and VARCHAR2 types.
nls_nchar_conv_excp	Specifies whether to report an error when data is lost during conversion between NCHAR/NVARCHAR2 and CHAR/VARCHAR2.
nls_numeric_characters	Specifies the characters used as decimal and thousand separators in string representations of numbers.
nls_currency	Specifies the local currency symbol for the number format element `L`.
nls_iso_currency	Specifies the local currency symbol for the number format element `C`.
nls_dual_currency	Specifies the local currency symbol for the number format element `U`.
nls_date_format	Specifies the format for converting the date type to a string and for implicitly converting a string to a date.
nls_timestamp_format	Specifies the format for converting the TIMESTAMP or TIMESTAMP_LTZ type to a string and for implicitly converting a string to a TIMESTAMP or TIMESTAMP_LTZ.
nls_timestamp_tz_format	Specifies the format for converting the TIMESTAMP_TZ type to a string and for implicitly converting a string to a TIMESTAMP_TZ.
explicit_defaults_for_timestamp	Specifies whether to enable non-standard behavior for the TIMESTAMP data type when handling default values and null values.
ob_default_lob_inrow_threshold	Specifies the default in-row storage threshold for LOB fields when creating a table.

Variable	Description
connect_timeout	Specifies the connection timeout period.
ob_pl_block_timeout	Specifies the maximum timeout period for PL.
cursor_sharing	Specifies whether to parameterize SQL statements.
innodb_strict_mode	Specifies the SQL check mode for specific syntaxes.
max_connections	Specifies the maximum number of connections for the tenant.
sql_mode	Specifies the SQL mode, which has a significant impact on operations such as data insertion.
sql_quote_show_create	Specifies whether to enable identifier quoting for `SHOW CREATE TABLE` and `SHOW CREATE DATABASE` statements.
ob_enable_rich_error_msg	Specifies whether to display information such as svr_ip, time, and trace_id when an SQL execution error occurs.
sql_throttle_current_priority	Specifies the throttling priority. Only requests from sessions where `sql_throttle_current_priority` is less than `sql_throttle_priority` will be throttled.
sql_throttle_priority	Specifies the throttling priority.
sql_throttle_rt	Specifies the response time (RT) value.
max_allowed_packet	Specifies the maximum size of network packets.
max_user_connections	Specifies the maximum number of connections that a single user can establish with an OBServer node. Setting this to `0` indicates no limit.
max_sp_recursion_depth	Specifies the maximum level of recursion for any specified stored procedure.
sql_select_limit	Specifies the maximum number of rows that a single SELECT query can return.
max_connections	Specifies the maximum number of connections for the tenant.
ob_enable_blk_nestedloop_join	Specifies whether to allow block nested loop joins.
parallel_max_servers	Specifies the maximum number of threads in the parallel execution (PX) thread pool on each server.

Variable	Description
optimizer_use_sql_plan_baselines	Specifies whether the optimizer uses a plan baseline.
optimizer_capture_sql_plan_baselines	Specifies whether to automatically capture new plans to a plan baseline.
parallel_degree_policy	Specifies the degree-of-parallelism selection strategy.
parallel_degree_limit	Specifies the maximum degree of parallelism that the optimizer selects when the auto DOP strategy is used.
parallel_min_scan_time_threshold	Specifies the minimum estimated execution time for parallel processing of base table scans.
optimizer_dynamic_sampling	Specifies the level of dynamic sampling.
optimizer_features_enable	Specifies the optimizer features enabled in different OceanBase Database versions.
ob_enable_transformation	Specifies whether to enable the rewrite feature of the SQL optimizer.

Auto-increment columns

Variable	Description
auto_increment_increment	Specifies the auto-increment step size. This variable is applicable only to MySQL clients.
auto_increment_offset	Specifies the starting value for the `AUTO_INCREMENT` column.
last_insert_id	Returns the last auto-increment field value inserted in the current session.
identity	The `identity` variable is a synonym for the `last_insert_id` variable. You can execute the `select @@identity` statement to query it.
sql_auto_is_null	Specifies whether to retrieve the auto-increment column value of the last inserted row.
auto_increment_cache_size	Specifies the number of cached auto-increment values.
div_precision_increment	Specifies the increment of the precision of the result of a division operation based on the precision of the dividend. This is a MySQL-compatible feature.

Character sets and strings

Variable	Description
character_set_client	The character set for statements sent by the client.
character_set_connection	The character set for statements received and then converted.
character_set_database	The character set for the default database.
character_set_results	The character set for converting result sets or error messages sent to the client.
character_set_server	The character set for the server.
character_set_system	The character set used by the server.
collation_connection	The character set and collation for the connection.
collation_database	The default character set and collation for creating databases.
collation_server	The default character set and collations for the server.
character_set_filesystem	The character set for the file system.
sql_warnings	Specifies whether to generate an informational string for a single-line INSERT statement when an alert is triggered.
init_connect	The string executed by the server for each client connection, consisting of one or more SQL statements separated by semicolons.
nls_sort	The collation for the string value.
nls_comp	The comparison rule for the string value.
nls_characterset	The default character set for CHAR, VARCHAR2, CLOB data types in the database.
nls_nchar_characterset	The default character set for NCHAR, NVARCHAR2, NCLOB data types in the database.

Session level

Variable name	Description
ob_last_schema_version	The schema version used in the latest operation in the session.

Variable name	Description
is_result_accurate	When a query contains the `topk` hint, the `is_result_accurate` variable indicates whether the result is accurate.

Variable name	Description
ob_capability_flag	The capabilities of the OBServer node, which are used for capability negotiation with the Proxy. This variable informs the Proxy of the features supported by the current OBServer node.
ob_proxy_partition_hit	Specifies whether to send SQL statements to the OBServer node hosting the primary partition of the data.
ob_org_cluster_id	The `CLUSTER_ID` of the OceanBase cluster.

Variable name	Description
ob_enable_show_trace	Specifies whether to use trace logs.
tracefile_identifier	Specifies the content to be added to the log for easier filtering of row iteration trace logs.

Time zones and dates

Variable name	Description
timestamp	`TIMESTAMP` indicates the timestamp in seconds. This variable affects the result of the `select now(6)` statement.
nls_language	This variable indicates the default language for database internationalization support, which is used for prompts, dates, month names, and the default values of `NLS_SORT` and `NLS_DATE_LANGUAGE`.

Transactions and transaction logs

Variable name	Description
ob_proxy_set_trx_executed	This variable specifies whether to send the `START TRANSACTION Syntax` statement to the OBServer through obproxy.
ob_trace_info	This variable is used to pass trace information, which can be persisted to the REDO log.

Auto-increment columns

Variable name	Description
last_insert_id	This variable returns the value of the last auto-increment column in the current session.

Previous topic

default_table_store_format

Next topic

auto_increment_cache_size

Overview of system variables|V4.3.0|OceanBase Database|docs|OceanBase Documentations