Purpose
You can use the
ALTER SYSTEM [SET] parameter_name = expressionstatement to modify a parameter.You can use the
ALTER SYSTEM [RESET] parameter_name = expressionstatement to reset a parameter.
OceanBase Database provides cluster-level and tenant-level parameters. Cluster-level parameters apply to all OBServer nodes in a cluster. Tenant-level parameters apply to all OBServer nodes in a tenant. For more information about parameters, see Overview.
Limitations and considerations
You can modify or reset a cluster-level parameter only from the
systenant.Generally, parameter settings take effect dynamically or upon the restart of OBServer nodes. Most parameters take effect dynamically without the need to restart OBServer nodes. Before you modify a parameter, you can execute the
SHOW PARAMETERS LIKEstatement to query its effective mode. For more information, see Cluster parameters.
Required privileges
To execute the ALTER SYSTEM [SET | RESET] parameter_name = expression statement, you must have the ALTER SYSTEM privilege. For more information about the privileges in OceanBase Database, see Privilege types in MySQL mode and Privilege types in Oracle mode.
Syntax
Change 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 value of the parameter after modification. |
| COMMENT | The comment to be added for 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 is BOTH. If you set SCOPE to BOTH or MEMORY for parameters whose settings cannot take effect immediately, an error is returned. |
| SERVER | The OBServer node for which you want to modify the parameter. You can specify only one OBServer node. |
| ZONE | The zone for which you want to modify the parameter. You can specify only one zone. If you specify a zone, the modification takes effect on all OBServer nodes in the zone. You cannot specify both ZONE and SERVER. |
| TENANT | The tenant for which you want to modify the parameter. If no tenant is specified, the modification takes effect on the current tenant. Valid values:
This clause is required only when you modify a tenant-level parameter for a specified tenant in 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 value of the parameter after reset. |
| SCOPE | The effective scope of the reset operation. Valid values:
The default value is BOTH. If you set SCOPE to BOTH or MEMORY for parameters whose settings cannot take effect immediately, an error is returned. |
| TENANT | The tenant for which the tenant-level parameter is to be reset. If no tenant is specified, the default value is the current tenant. This clause is required only when you modify a tenant-level parameter for a specified tenant in the sys tenant. For more information about tenant-level parameters, see Tenant-level parameters. |
Examples
Modify parameters
In the
systenant, modify the cluster-level parameterenable_sql_audit.obclient [oceanbase]> ALTER SYSTEM SET ENABLE_SQL_AUDIT = False SCOPE = BOTH;In the
systenant, modify the tenant-level parameterconnection_control_max_connection_delayfortenant.obclient [oceanbase]> ALTER SYSTEM SET connection_control_max_connection_delay = 2147483646 TENANT = tenant;In the
systenant, modify the tenant-level parametermajor_freeze_duty_timefor thesystenant, all user tenants, and all meta tenants in the cluster.Sequentially execute the following statements in the
systenant: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_timefor the current tenant.obclient [oceanbase]> ALTER SYSTEM SET major_freeze_duty_time='01:00';In the
systenant, reset the cluster-level parameterenable_sql_auditto its default value. The modification takes effect immediately and is persisted toSPFILE.obclient [oceanbase]> ALTER SYSTEM RESET ENABLE_SQL_AUDIT SCOPE = BOTH;
Reset parameters
Reset the
log_disk_utilization_thresholdparameter.obclient> ALTER SYSTEM RESET log_disk_utilization_threshold;Modify a tenant-level parameter for all tenants or a specified tenant in the sys tenant.
obclient> ALTER SYSTEM RESET log_disk_utilization_threshold TENANT='ALL'; obclient> ALTER SYSTEM RESET log_disk_utilization_threshold TENANT='Oracle';