PARAMETER

Description

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

• The ALTER SYSTEM [RESET] parameter_name = expression statement is used to reset the value of a parameter.

OceanBase Database provides cluster-level parameters and tenant-level parameters. A cluster-level parameter takes effect on all nodes in the cluster, and a tenant-level parameter takes effect on all nodes in the current tenant. For more information, see Overview.

Limitations and considerations

• Only the sys tenant can modify or reset cluster-level parameters. User tenants are not allowed to modify or reset cluster-level parameters.

• Parameters take effect in two ways: dynamically and on restart. Most parameters take effect dynamically, which means that you do not need to restart the OBServer node for the changes to take effect. Before you modify a parameter, you can execute the SHOW PARAMETERS LIKE statement to confirm how the parameter takes effect. For more information, see Cluster parameters.

Privilege requirements

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

Syntax

Modify the value of a 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 parameter

ALTER SYSTEM [RESET] alter_system_set_parameter_action [, alter_system_set_parameter_action...]

alter_system_set_parameter_action:
parameter_name = expression [SCOPE = {MEMORY | SPFILE | BOTH}] [TENANT [=] tenant_name]

Parameters

Examples

Modify a parameter

• Modify the value of the cluster-level parameter enable_sql_audit in the sys tenant.

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

• Modify the value of the tenant-level parameter connection_control_max_connection_delay in the tenant tenant in the sys tenant.

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

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

  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;
  

• Modify the value of the tenant-level parameter major_freeze_duty_time in the current tenant.

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

• Reset the value of the cluster-level parameter enable_sql_audit to its default value in the sys tenant. The change takes effect immediately and is also updated in the SPFILE.

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

Reset a parameter

• Here is an example of resetting the log_disk_utilization_threshold parameter:

  obclient> ALTER SYSTEM RESET log_disk_utilization_threshold;
  

• In the sys tenant, modify or reset all or specified tenant parameters. Here is an example:

  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