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 divided into cluster-level 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 effective method of a configuration parameter is generally divided into dynamic and restart-effective methods. Most configuration parameters are dynamic, meaning they take effect without requiring a restart of the OBServer node. Before modifying a configuration parameter, you can use the SHOW PARAMETERS LIKE statement to confirm its effective method. For more information about how to view the effective method 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 OceanBase Database privileges, see Privilege classification in MySQL mode and Privilege classification in Oracle 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 items

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

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

• In the sys tenant, modify the tenant-level configuration item 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 configuration item major_freeze_duty_time of all tenants (including the sys tenant, user tenants, and Meta tenants) 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 configuration item 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 configuration item 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 configuration items

• The following example shows how to modify the log_disk_utilization_threshold configuration item:

  obclient> ALTER SYSTEM RESET log_disk_utilization_threshold;
  

• In the sys tenant, modify the tenant-level configuration items 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 items and system variables