Description
The
ALTER SYSTEM [SET] parameter_name = expressionstatement is used to modify the value of a parameter.The
ALTER SYSTEM [RESET] parameter_name = expressionstatement 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
systenant can modify or reset cluster-level parameters. User tenants are not allowed to modify or reset cluster-level parameters.Parameters take effect dynamically or upon 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 LIKEstatement 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
| Parameter | Description |
|---|---|
| parameter_name | The name of the parameter to be modified. |
| expression | The new value of the parameter. |
| COMMENT | Comments on the modification. This parameter is optional, but we recommend that you do not skip it. |
| SCOPE | Specifies the effective scope of this configuration item modification, which mainly includes the following three types:
The default value of SCOPE is BOTH. |
| SERVER | The OBServer node in the cluster whose parameter value is to be modified. You can specify only one OBServer node. |
| ZONE | The zone in the cluster whose parameter value is to be modified. You can specify only one zone. The modification takes effect on all OBServer nodes in the zone. You cannot specify both the Zone and Server parameters. |
| TENANT | The tenant in which the parameter value is to be modified. If you do not specify this parameter, the current tenant is used by default. Among them:
This clause is required when you want to modify the tenant-level parameter value in a specified tenant from the sys tenant. For more information about tenant-level parameters, see Tenant-level 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
| Parameter | Description |
|---|---|
| parameter_name | The name of the parameter to be reset. |
| expression | The parameter to be reset. |
| SCOPE | The scope of the parameter reset, which can be one of the following:
The default value of SCOPE is BOTH. |
| TENANT | The tenant under the sys tenant in which the tenant-level parameter value is to be reset. If this parameter is not specified, the current tenant is used. This clause is required only when you modify a tenant-level parameter in the sys tenant for a specified tenant. For more information about tenant-level parameters, see Tenant-level parameters. |
Examples
Modify a parameter
Modify the value of the cluster-level parameter
enable_sql_auditin 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_delayin thetenanttenant 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_timein all tenants in the cluster (including the sys tenant, user tenants, and meta tenants) in the sys tenant.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;Modify the value of the tenant-level parameter
major_freeze_duty_timein the current tenant.obclient [oceanbase]> ALTER SYSTEM SET major_freeze_duty_time='01:00';Reset the value of the cluster-level parameter
enable_sql_auditto its default value in the sys tenant. The change takes effect immediately and is also updated in theSPFILE.obclient [oceanbase]> ALTER SYSTEM RESET ENABLE_SQL_AUDIT SCOPE = BOTH;
Reset a parameter
Here is an example of modifying the
log_disk_utilization_thresholdparameter:obclient> ALTER SYSTEM RESET log_disk_utilization_threshold;Modify a tenant-level parameter in the sys tenant. Here are some examples:
obclient> ALTER SYSTEM RESET log_disk_utilization_threshold TENANT='ALL'; obclient> ALTER SYSTEM RESET log_disk_utilization_threshold TENANT='Oracle';