Purpose
The
ALTER SYSTEM [SET] parameter_name = expressionstatement is used to modify the value of a parameter.The
ALTER SYSTEM RESET parameter_namestatement is used to reset the value of a parameter.
Configuration parameters in OceanBase AI Database are divided into cluster-level parameters and tenant-level parameters. Cluster-level parameters apply to all nodes in the cluster, while tenant-level parameters apply to all nodes where the current tenant resides. For more information about parameters, see Overview of parameters and system variables.
Limitations and considerations
Only the
systenant can modify or reset cluster-level parameters. User tenants cannot modify or reset cluster-level parameters.Parameter effectiveness generally falls into two categories: dynamic and restart. Most parameters are dynamic, meaning they take effect without restarting the OBServer node. Before modifying a parameter, you can use the
SHOW PARAMETERS LIKEstatement to confirm its effectiveness mode. For detailed operations on checking the effectiveness mode of parameters, see Cluster 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 AI Database privileges, see Permission classification.
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
Parameters |
Description |
|---|---|
| parameter_name | Specifies the name of the parameter to be modified. |
| expression | Specifies the value of the modified parameter. |
| COMMENT | Add comments about this modification. This parameter is optional, but it is recommended that you do not omit it. |
| SCOPE | Specifies the scope of effect for this parameter modification. The main scopes are as follows:
The default value of SCOPE is BOTH. |
| SERVER | Specify the OBServer node in the cluster whose parameter value needs to be modified. Only one OBServer node can be specified. |
| ZONE | Specify the zone in the cluster whose parameter value needs to be modified. Only one zone can be specified. After a zone is specified, the modification takes effect on all OBServer nodes within that zone. You cannot specify both a zone and a server. |
| TENANT | The system tenant specifies the tenant whose parameter values are to be modified. If not specified, the current tenant is used by default. Valid values:
Specify only by sys This clause is required when modifying tenant-level parameters for a specified tenant. For a list of tenant-level parameters, see Parameter overview. |
Reset the value of a parameter
ALTER SYSTEM RESET parameter_name [SCOPE = {MEMORY | SPFILE | BOTH}] [TENANT [=] tenant_name];
Parameters
Parameters |
Description |
|---|---|
| parameter_name | Specifies the name of the parameter to be reset. |
| SCOPE | Specifies the scope of effect for this parameter reset. The main scopes are as follows:
The default value of SCOPE is BOTH. |
| TENANT | Specifies the tenant whose parameter values need to be reset. If not specified, the current tenant is used by default. Only available when the system tenant has the sys This clause is required when modifying tenant-level parameters for a specified tenant. For a list of tenant-level parameters, see Parameter overview. |
Examples
Modify parameters
The system tenant modifies the value of the cluster-level parameter
enable_sql_audit.obclient [oceanbase]> ALTER SYSTEM SET ENABLE_SQL_AUDIT = False SCOPE = BOTH;The system tenant modifies the value of the tenant-level parameter
connection_control_max_connection_delayfor thetenanttenant.obclient [oceanbase]> ALTER SYSTEM SET connection_control_max_connection_delay = 2147483646 TENANT = tenant;The system tenant modifies 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.You must execute the following commands in sequence under 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;A user tenant modifies the value of the tenant-level parameter
major_freeze_duty_timefor itself.obclient [oceanbase]> ALTER SYSTEM SET major_freeze_duty_time='01:00';The system tenant resets the value of the cluster-level parameter
enable_sql_auditto its default value. The change takes effect immediately and is also updated to theSPFILE.obclient [oceanbase]> ALTER SYSTEM RESET ENABLE_SQL_AUDIT SCOPE = BOTH;
Reset parameters
The following example shows how to modify the
log_disk_utilization_thresholdparameter:obclient> ALTER SYSTEM RESET log_disk_utilization_threshold;Modify tenant-level parameters for all or specified tenants under the sys tenant. Example:
obclient> ALTER SYSTEM RESET log_disk_utilization_threshold TENANT='ALL'; obclient> ALTER SYSTEM RESET log_disk_utilization_threshold TENANT='mysql';