PARAMETER|V4.2.2|OceanBase Database| docs|Distributed Database

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 sys tenant 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 LIKE statement 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`. `MEMORY`: specifies to modify only the value of the parameter in memory, the modification takes effect immediately, and the modification will be lost after the OBServer node is restarted. (Currently, no parameter supports this method.) `SPFILE`: specifies to modify only the value of the parameter in the configuration table, and the modification takes effect only after the OBServer node is restarted. `BOTH`: specifies to modify the value of the parameter in the configuration table and in memory. The modification takes effect immediately and will not be lost after the OBServer node is restarted. 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. `sys`: specifies to modify the parameter value in the sys tenant. `all_user` and `all`: specifies to modify the parameter value in all user tenants. Note Starting from V4.2.1, OceanBase Database allows you to use `TENANT = all_user` and `TENANT = all` interchangeably to modify the parameter value in all user tenants. We recommend that you use `TENANT = all_user` when you need to modify the parameter value in all user tenants. `TENANT = all` will be deprecated. `all_meta`: specifies to modify the parameter value in all meta tenants. `tenant_name`: specifies a tenant. You can specify only one tenant each time. 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_audit in 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_delay in the tenant tenant 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_time for 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_time in the current tenant.
```
obclient [oceanbase]> ALTER SYSTEM SET major_freeze_duty_time='01:00';
```

OceanBase

Customer Stories

Documentation

PARAMETER