OceanBase Database

SQL - V4.2.1

Download PDF

Overview

Last Updated:2026-09-04 09:45:16

This topic lists the system variables of OceanBase Database in detail based on their intended use.

Auto-increment column variables

Variable	Description
auto_increment_increment	The auto-increment step size. It is used only for MySQL client logon.
auto_increment_offset	The start value of an auto-increment column.
last_insert_id	The last inserted auto-increment value in the current session.
identity	The `identity` variable is synonymous with the `last_insert_id` variable and can be queried by using `select @@identity`.
sql_auto_is_null	Specifies whether to obtain the auto-increment column value of the last inserted row.
auto_increment_cache_size	The number of cached auto-increment values.
div_precision_increment	The number of digits by which to increase the scale of the result of a division operation based on the scale of the dividend. This variable is for compatibility with MySQL.

Transaction variables

Variable	Description
validate_password_check_user_name	Specifies whether a user password can 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 supports the percentage sign (%), underscore (_), and IP addresses (separated by 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 encryption and decryption functions.
sql_safe_updates	Specifies whether to enable the MySQL SQL security mode. The default value is OFF.
pid_file	Specifies the path name of the file in which the server writes its process ID.
port	Specifies the port number on which the server listens for TCP/IP connections.
socket	Specifies the name of the `Socket` file for local client connections.

Version information

Variable name	Description
version_comment	Displays the version information of the OBServer node.
version	Displays the server version number.
ob_last_schema_version	Specifies the latest schema version used in the 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 enable packet checksum verification between the OBproxy and the OBServer node. This setting takes effect immediately.

Debug

Variable name	Description
debug_sync	Sets the synchronization point during Debug mode, and is compatible with MySQL.
ob_global_debug_sync	Sets the synchronization point during Debug mode. This variable affects all sessions and is different from `debug_sync`.

Case sensitivity

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

Variable name	Description
query_cache_size	The memory size allocated for storing historical query results, 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, `is_result_accurate` 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 read queries.
net_read_timeout	The number of seconds to wait for other data to be received on the connection before interrupting the read.
net_write_timeout	The number of seconds to wait for block writes on the connection 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. If the queuing time is greater than or equal to this value, queries are not executed.
parallel_servers_target	The maximum number of servers on which a large query is queued.
cte_max_recursion_depth	The maximum recursion depth of a common table expression (CTE). The server terminates any operations exceeding this CTE value.
regexp_stack_limit	The maximum available memory for the internal stack used in regular expression matching operations.
regexp_time_limit	The time limit for regular expression matching operations.

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 and DDL operations.

Replicas

Variable	Description
ob_create_table_strict_mode	Specifies the strict mode for table creation.
ob_default_replica_num	Specifies the default number of replicas for each 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.

Caches

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

Functions

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

Cluster

Variable	Description
ob_capability_flag	The capabilities that the OBServer node provides for connection with the Proxy. This variable is used for capability negotiation with the Proxy and indicates to the Proxy which features the OBServer node supports and which features it does not support.
ob_compatibility_mode	The compatibility mode of the tenant.
ob_route_policy	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. However, setting this variable does not take effect and does not return an error.
resource_manager_plan	The resource management plan to be activated. Different plans use different CPU quotas to limit user activities in the resource group.
license	The license type.
ob_proxy_partition_hit	Specifies whether to send SQL statement requests to the OBServer node where the primary data partitions are located.
ob_org_cluster_id	The `CLUSTER_ID` of the OceanBase cluster.
server_uuid	The UUID of the OBServer node.
default_storage_engine	The default storage engine of the OBServer node.

Variable	Description
datadir	The local disk path for storing data.
plugin_dir	The path for storing plugin DLLs.
secure_file_priv	The path that allows access during file import or export.
ob_enable_hash_group_by	Specifies whether to enable Hash Group by.

Variable	Description
ob_sql_work_area_percentage	The maximum percentage of tenant memory that can be used 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 maximum percentage of tenant disk space for temporary tables.

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

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

Variable	Description
time_zone	Specifies the time zone for the current tenant session. Valid values are in the `+08:00` offset format or the `Asia/Shanghai` region format.
timestamp	The `TIMESTAMP` is a timestamp in seconds. The value of this variable affects the result of the `select now(6)` statement.
system_time_zone	Specifies the system time zone of the server.
error_on_overlap_time	Specifies whether to return an error when the time zone overlaps occur and the time is ambiguous.
nls_language	Specifies 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`.
nls_territory	Specifies the current region for database internationalization support, which is used in combination with `nls_language`.
nls_date_language	Specifies the default language for dates in database internationalization support.
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` to OBServer through obproxy.
ob_early_lock_release	Specifies whether to enable the early lock release (ELR) feature for transactions. Note This variable is deprecated in 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, which is the time interval between the execution of two statements in a transaction that exceeds the timeout 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 maximum time that a transaction waits for a lock to be released.
tx_read_only	Specifies whether the transaction is read-only.
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 data types.
nls_nchar_conv_excp	Specifies whether to return an error when data is lost during conversion between NCHAR and NVARCHAR2 data types and CHAR and VARCHAR2 data types.
nls_numeric_characters	Specifies the characters used as the decimal separator and the thousand separator in string representations of numbers.
nls_currency	Specifies the local currency symbol for the `L` format element in number formatting.
nls_iso_currency	Specifies the local currency symbol for the `C` format element in number formatting.
nls_dual_currency	Specifies the local currency symbol for the `U` format element in number formatting.
nls_date_format	Specifies the format for converting date data to a string and for implicitly converting a string to a date.
nls_timestamp_format	Specifies the format for converting TIMESTAMP or TIMESTAMP_LTZ data to a string and for implicitly converting a string to TIMESTAMP or TIMESTAMP_LTZ.
nls_timestamp_tz_format	Specifies the format for converting TIMESTAMP_TZ data to a string and for implicitly converting a string to 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 perform parameterization when processing SQL statements.
innodb_strict_mode	Specifies the SQL check mode for specific syntaxes.
max_connections	Specifies the maximum number of connections for the entire tenant.
sql_mode	Specifies the SQL mode, which significantly affects behaviors 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 error occurs during SQL execution.
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 a network packet.
max_user_connections	Specifies the maximum number of connections that a single user can establish to an OBServer node. Set this parameter to `0` to indicate unlimited connections.
max_sp_recursion_depth	Specifies the maximum number of recursive calls to 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 entire 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 plan baselines.
optimizer_capture_sql_plan_baselines	Specifies whether to automatically capture new plans to plan baselines.
parallel_degree_policy	Specifies the 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 of different OceanBase versions.
ob_enable_transformation	Specifies whether to enable the rewrite feature of the SQL optimizer.
range_index_dive_limit	Specifies the maximum number of range indexes that the optimizer uses when estimating rows based on the storage layer.
partition_index_dive_limit	Specifies the maximum number of partitions that the optimizer uses when estimating rows based on the storage layer.

Auto-increment columns

Variable	Description
auto_increment_increment	Specifies the auto-increment step, which is valid only for MySQL clients.
auto_increment_offset	Specifies the starting value of the `AUTO_INCREMENT` column.
last_insert_id	Returns the value of the auto-increment field for the last inserted row in the current session.
identity	`identity` and `last_insert_id` are synonyms. You can run the `select @@identity` statement to query the value.
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 to which the received statements should be converted.
character_set_database	The character set for the default database.
character_set_results	The character set to which the server should convert the result set or error message before sending it back 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 the created database.
collation_server	The default character set and collation for the server.
character_set_filesystem	The character set type for the file system.
sql_warnings	Controls whether to generate an information string for a single-line INSERT statement when an alert is triggered.
init_connect	The string that the server executes 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, and other data types in the database.
nls_nchar_characterset	The default character set for NCHAR, NVARCHAR2, NCLOB, and other data types in the database.

Session-level

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

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

Variable name	Description
ob_capability_flag	The capabilities of the OBServer node, used for capability negotiation with the Proxy. It informs the Proxy of the features supported and not supported by the current OBServer node.
ob_proxy_partition_hit	Specifies whether to send SQL statements to the OBServer nodes hosting the primary partitions.
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.

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

Variable name	Description
ob_proxy_set_trx_executed	Specifies whether to send the `START TRANSACTION Syntax` statement to the OBServer through obproxy.
ob_trace_info	Specifies the trace information to be passed through. This information can be persisted to the redo log.

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

Previous topic

compaction_schedule_tablet_batch_cnt

Next topic

auto_increment_cache_size