Description
The ALTER SYSTEM [SET] parameter_name = expression statement is used to modify the value of a parameter.
In OceanBase Database, parameters can be cluster-level or tenant-level. A cluster-level parameter takes effect on all nodes in the cluster, whereas 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 cluster-level parameters. User tenants are not allowed to modify cluster-level parameters.Parameters take effect either dynamically or after a restart. Most parameters take effect dynamically, which means that you do not need to restart the OBServer node for the modification to take effect. Before you modify a parameter, you can execute the
SHOW PARAMETERS LIKEstatement to verify whether the parameter takes effect dynamically. For more information, see Cluster parameters.
Privileges
To execute the ALTER SYSTEM [SET] parameter_name = expression statement, the current user 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
ALTER SYSTEM [SET] alter_system_set_parameter_actions;
alter_system_set_parameter_actions:
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 about the modification. This parameter is optional, but we recommend that you do not ignore it. |
| SCOPE | The effective scope of the parameter modification. Valid values: MEMORY, SPFILE, and BOTH.
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 OBServer nodes have their parameter values modified. You can specify only one zone. The modification takes effect on all OBServer nodes in the zone. You cannot specify both Zone and Server. |
| TENANT | The tenant in which the value of the specified tenant-level parameter is to be modified. The default value is the current tenant.
This clause is required when you want to modify the value of a tenant-level parameter from the sys tenant. For more information about tenant-level parameters, see Tenant-level parameters. |
Examples
Change the value of the cluster-level parameter
enable_sql_auditin the sys tenant.obclient [oceanbase]> ALTER SYSTEM SET ENABLE_SQL_AUDIT = False SCOPE = BOTH;Change 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;Change 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 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;Change 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';