PARAMETER

Purpose

• The ALTER SYSTEM [SET] parameter_name = expression statement is used to modify the value of a configuration item.

• The ALTER SYSTEM RESET parameter_name statement is used to reset the value of a configuration item.

In OceanBase Database, configuration items are divided into cluster-level and tenant-level. Cluster-level configuration items apply to all nodes in the cluster, while tenant-level configuration items apply to all nodes of the current tenant. For more information about configuration items, see Overview of configuration items and system variables.

Limitations and considerations

• Only the sys tenant can modify or reset cluster-level configuration items. User tenants cannot modify or reset cluster-level configuration items.

• The effectiveness of configuration items is generally divided into dynamic effectiveness and restart effectiveness. Most configuration items are dynamically effective, meaning they take effect without the need to restart the OBServer node. Before modifying a configuration item, you can use the SHOW PARAMETERS LIKE statement to confirm its effectiveness. For more information about how to check the effectiveness of configuration items, see Cluster-level configuration items.

Privilege requirements

To execute the ALTER SYSTEM [SET] parameter_name = expression and ALTER SYSTEM RESET parameter_name statements, the current user must have the ALTER SYSTEM privilege. For more information about privileges in OceanBase Database, see Privilege classification in MySQL mode and Privilege classification in Oracle mode.

Syntax

Modify the value of a configuration item

ALTER SYSTEM [SET] alter_system_set_parameter_action [, alter_system_set_parameter_action...]

alter_system_set_parameter_action:
parameter_name = expression [COMMENT [=] 'text'] [SCOPE = {MEMORY | SPFILE | BOTH}] [SERVER [=] 'ip:port' | ZONE [=] 'zone_name' | TENANT [=] {sys | all_user | all | all_meta | tenant_name}]

Parameters

Reset the value of a configuration item

ALTER SYSTEM RESET parameter_name [SCOPE = {MEMORY | SPFILE | BOTH}] [TENANT [=] tenant_name];

Parameters

Examples

Modify a configuration parameter

• In the sys tenant, modify the cluster-level parameter enable_sql_audit.

  obclient [oceanbase]> ALTER SYSTEM SET ENABLE_SQL_AUDIT = False SCOPE = BOTH;
  

• In the sys tenant, modify the tenant-level parameter connection_control_max_connection_delay of the tenant tenant.

  obclient [oceanbase]> ALTER SYSTEM SET connection_control_max_connection_delay = 2147483646 TENANT = tenant;
  

• In the sys tenant, modify the tenant-level parameter major_freeze_duty_time of all tenants (including the sys tenant, user tenants, and meta tenant) in the cluster.

  You must execute the following commands in the sys tenant:

  obclient [oceanbase]> ALTER SYSTEM SET major_freeze_duty_time='01:00' TENANT = sys;
  

  obclient [oceanbase]> ALTER SYSTEM SET major_freeze_duty_time='01:00' TENANT = all_user;
  

  obclient [oceanbase]> ALTER SYSTEM SET major_freeze_duty_time='01:00' TENANT = all_meta;
  

• In the user tenant, modify the tenant-level parameter major_freeze_duty_time.

  obclient [oceanbase]> ALTER SYSTEM SET major_freeze_duty_time='01:00';
  

• In the sys tenant, reset the cluster-level parameter enable_sql_audit to its default value. The change takes effect immediately and is also written to the SPFILE.

  obclient [oceanbase]> ALTER SYSTEM RESET ENABLE_SQL_AUDIT SCOPE = BOTH;
  

Reset a configuration parameter

• The following example shows how to modify the log_disk_utilization_threshold parameter:

  obclient> ALTER SYSTEM RESET log_disk_utilization_threshold;
  

• In the sys tenant, modify the tenant-level parameters of all or specified tenants. The following example shows how to do this:

  obclient> ALTER SYSTEM RESET log_disk_utilization_threshold TENANT='ALL';
  obclient> ALTER SYSTEM RESET log_disk_utilization_threshold TENANT='Oracle';
  

References

• Set parameters

• Overview of configuration parameters and system variables

• Reset cluster parameters