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 for all nodes in the cluster, and a tenant-level parameter takes effect for 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 in two ways: dynamically and on restart. Most parameters take effect dynamically, which means you can modify a parameter without restarting the OBServer node. 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.
Privileges required
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 | The comment on the modification. This parameter is optional, but we recommend that you do not skip it. |
| SCOPE | The scope in which the parameter modification takes effect. Valid values: MEMORY, SPFILE, and BOTH.
The default value 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 specified zone. You cannot specify both Zone and Server. |
| 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 that the parameter reset takes effect in. Valid values:
The default value of SCOPE is BOTH. |
| TENANT | The tenant that specifies the value of the tenant-level parameter to be reset. If this parameter is not specified, the current tenant is used as the default value. You need to add this clause 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_delayfor 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_timefor all tenants in the cluster (including the sys tenant, user tenants, and meta tenant) 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_timefor the current tenant in the 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 resetting the
log_disk_utilization_thresholdparameter:obclient> ALTER SYSTEM RESET log_disk_utilization_threshold;In the sys tenant, modify or reset all or specified tenant-level parameters. Here are some examples:
obclient> ALTER SYSTEM RESET log_disk_utilization_threshold TENANT='ALL'; obclient> ALTER SYSTEM RESET log_disk_utilization_threshold TENANT='Oracle';