PARAMETER

Purpose

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

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

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

Limitations and considerations

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

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

Privilege requirements

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

Syntax

Modify the value of a configuration parameter

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 parameter

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

Parameters

Examples

Modify configuration parameters

• 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 in the cluster, including the sys tenant, user tenants, and Meta tenants.

  You need to 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 a user tenant, modify the tenant-level parameter major_freeze_duty_time of the current tenant.

  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 updated to the SPFILE.

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

Reset configuration parameters

• 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