Purpose
You can use the ALTER SYSTEM [SET] parameter_name = expression statement to modify the value of a parameter.
OceanBase Database provides cluster-level and tenant-level parameters. A cluster-level parameter applies to all nodes in the cluster. A tenant-level parameter applies to all nodes where the current tenant resides. For more information about parameters, see Overview.
Limitations and considerations
You can modify a cluster-level parameter only from the sys tenant, but not from a user tenant.
A parameter can take effect dynamically or upon a restart of the OBServer node. Most parameters take effect dynamically. Before you modify a parameter, you can execute the
SHOW PARAMETERS LIKEstatement to check its effective mode. For more information about how to query the effective mode of a parameter, see Cluster parameters.
Required privileges
You must have the ALTER SYSTEM privilege to execute the ALTER SYSTEM [SET] parameter_name = expression statement. For more information about privileges in OceanBase Database, see Privilege types in MySQL mode and Privilege types in Oracle mode.
Syntax
ALTER SYSTEM [SET] alter_system_set_parameter_actions;
alter_system_set_parameter_actions:
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
| Parameter | Description |
|---|---|
| parameter_name | The name of the parameter to be modified. |
| expression | The value of the parameter after modification. |
| COMMENT | The comment on the modification. This parameter is optional. We recommend that you specify this parameter. |
| SCOPE | The effective scope of the parameter modification. Valid values:
The default value of SCOPE is BOTH. You set SCOPE to BOTH or MEMORY for parameters on which modifications do not take effect immediately, an error will be returned. |
| SERVER | The OBServer node in the cluster on which the parameter value is to be modified. You can specify only one OBServer node. |
| ZONE | The zone in the cluster in which the parameter value is to be modified. You can specify only one zone. After you specify a zone, the modification takes effect on all OBServer nodes in the specified zone. You cannot specify both ZONE and SERVER. |
| TENANT | The tenant for which the tenant-level parameter is to be modified from the sys tenant. By default, if you do not specify this parameter, the parameter is modified for the current tenant. Valid values:
This parameter is required only when you modify a tenant-level parameter for the specified tenant from the sys tenant. For the list of tenant-level parameters, see Tenant-level parameters. |
Examples
Modify the value of the cluster-level parameter
enable_sql_auditfrom 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_delayfor thetenanttenant from 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_timefor all tenants (including the sys tenant, user tenants, and meta tenants) in the current cluster from the sys tenant.Execute the following statements in the sys tenant in sequence:
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_timefor a user tenant from the current tenant.obclient [oceanbase]> ALTER SYSTEM SET major_freeze_duty_time='01:00';