Purpose
The
ALTER SYSTEM [SET] parameter_name = expressionstatement is used to modify the value of a configuration parameter.The
ALTER SYSTEM RESET parameter_namestatement is used to reset the value of a configuration parameter.
In OceanBase Database, configuration parameters are categorized into cluster-level parameters and tenant-level parameters. Cluster-level parameters apply to all nodes in the entire cluster, while tenant-level parameters apply to all nodes of the current tenant. For more information about configuration parameters, see Overview of configuration 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.The effectiveness of configuration parameters is generally divided into two types: dynamic effectiveness and restart effectiveness. Most configuration parameters are dynamically effective, meaning they take effect without the need to restart the OBServer node. Before modifying a configuration parameter, you can use the
SHOW PARAMETERS LIKEstatement to confirm its effectiveness. For more information about how to view the effectiveness of a configuration parameter, see Cluster-level configuration 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 the privileges in OceanBase Database, see Privilege types in MySQL-compatible mode and Privilege types in Oracle-compatible mode.
Syntax
Modify the value of a configuration 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 | Specifies the name of the configuration parameter to be modified. |
| expression | Specifies the new value of the configuration parameter. |
| COMMENT | Adds a comment about the modification. This parameter is optional, but it is recommended to include it. |
| SCOPE | Specifies the scope of the configuration parameter modification. The options are as follows:
The default value of SCOPE is BOTH. |
| SERVER | Specifies the OBServer node in the cluster where the configuration parameter value is to be modified. Only one OBServer node can be specified. |
| ZONE | Specifies the zone in the cluster where the configuration parameter value is to be modified. Only one zone can be specified. After specifying a zone, the modification takes effect on all OBServer nodes in the zone. You cannot specify both a zone and a server. |
| TENANT | Specifies the tenant where the tenant-level configuration parameter value is to be modified. If not specified, the default is the current tenant. The options are as follows:
This clause is required only when the sys tenant modifies the tenant-level configuration parameter of a specified tenant. For a list of tenant-level configuration parameters, see Tenant-level configuration parameters. |
Reset the value of a configuration parameter
ALTER SYSTEM RESET parameter_name [SCOPE = {MEMORY | SPFILE | BOTH}] [TENANT [=] tenant_name];
Parameters
| Parameter | Description |
|---|---|
| parameter_name | Specifies the name of the configuration parameter to be reset. |
| SCOPE | Specifies the scope of the configuration parameter reset. The options are as follows:
The default value of SCOPE is BOTH. |
| TENANT | Specifies the tenant where the tenant-level configuration parameter value is to be reset. If not specified, the default is the current tenant. This clause is required only when the sys tenant modifies the tenant-level configuration parameter of a specified tenant. For a list of tenant-level configuration parameters, see Tenant-level configuration parameters. |
Examples
Modify configuration parameters
In the sys tenant, modify the cluster-level parameter
enable_sql_audit.obclient [oceanbase]> ALTER SYSTEM SET ENABLE_SQL_AUDIT = False SCOPE = BOTH;In the sys tenant, modify the tenant-level parameter
connection_control_max_connection_delayof thetenanttenant.obclient [oceanbase]> ALTER SYSTEM SET connection_control_max_connection_delay = 2147483646 TENANT = tenant;In the sys tenant, modify the tenant-level parameter
major_freeze_duty_timeof all tenants in the cluster, including the sys tenant, user tenants, and Meta tenants.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;In a user tenant, modify the tenant-level parameter
major_freeze_duty_timeof the current tenant.obclient [oceanbase]> ALTER SYSTEM SET major_freeze_duty_time='01:00';In the sys tenant, reset 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 configuration parameters
The following example shows how to modify the
log_disk_utilization_thresholdparameter:obclient> ALTER SYSTEM RESET log_disk_utilization_threshold;In the sys tenant, modify the tenant-level parameters of all or specified tenants. The following example shows how to do this:
obclient> ALTER SYSTEM RESET log_disk_utilization_threshold TENANT='ALL'; obclient> ALTER SYSTEM RESET log_disk_utilization_threshold TENANT='Oracle';