Purpose
You can use this statement to modify parameters.
Note
You can modify cluster-level parameters only in the sys tenant.
Syntax
ALTER SYSTEM [SET] parameter_name = expression [SCOPE = {MEMORY | SPFILE | BOTH}]
[COMMENT [=] 'text'][TENANT [=] all | all_user | all_meta | tenant_name][SERVER [=] 'ip:port' | ZONE [=] 'zone'];
Parameters
| Parameter | Description |
|---|---|
| parameter_name | The name of the parameter to be modified. |
| expression | The value of the parameter after modification. |
| COMMENT | Adds a comment on the modification. This parameter is optional. We recommend that you specify this parameter. |
| SCOPE | The effective scope of the parameter modification. Valid values:
SCOPE is BOTH. If SCOPE is set to BOTH or MEMORY for parameters on which the modifications do not take effect immediately, an error is returned. |
| SERVER | Specifies to modify only a parameter of the specified server instance. |
| ZONE | Specifies to modify the parameter for servers of the specified type in the specified cluster. If this parameter is not specified, the modification applies to servers of the specified type in all clusters. |
| TENANT | The tenants for which you want to modify tenant-level parameters in the sys tenant, which is the current tenant by default. It can be all user tenants, all meta tenants, or specified tenants. This parameter is required when you modify tenant-level parameters of a specified tenant in the sys tenant. For more information, see Tenant-level parameters.
|
Note
- When you modify multiple parameters at a time, separate the parameters with commas (,).
- You can use the following statement to view system parameters:
SHOW PARAMETERS [LIKE 'pattern' | WHERE expr];. TENANT = all_userandTENANT = allshare the same semantics in OceanBase Database V4.2.1 and later. We recommend that you useTENANT = all_userto indicate all user tenants, becauseTENANT = allwill be deprecated in future versions.
Examples
Modify the
enable_sql_auditparameter.obclient> SHOW PARAMETERS LIKE 'ENABLE_SQL_AUDIT'; +------+----------+---------------+----------+------------------+-----------+-------+---------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+ | zone | svr_type | svr_ip | svr_port | name | data_type | value | info | section | scope | source | edit_level | +------+----------+---------------+----------+------------------+-----------+-------+---------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+ | z1 | observer | xx.xx.xxx.xx1 | xxxxx | enable_sql_audit | NULL | True | specifies whether SQL audit is turned on. The default value is TRUE. Value: TRUE: turned on FALSE: turned off | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | +------+----------+---------------+----------+------------------+-----------+-------+---------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+ 1 rows in set obclient> ALTER SYSTEM SET ENABLE_SQL_AUDIT = FALSE SCOPE = BOTH; Query OK, 0 rows affected obclient> SHOW PARAMETERS LIKE 'ENABLE_SQL_AUDIT'; +------+----------+---------------+----------+------------------+-----------+-------+---------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+ | zone | svr_type | svr_ip | svr_port | name | data_type | value | info | section | scope | source | edit_level | +------+----------+---------------+----------+------------------+-----------+-------+---------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+ | z1 | observer | xx.xx.xxx.xx1 | xxxxx | enable_sql_audit | NULL | False | specifies whether SQL audit is turned on. The default value is TRUE. Value: TRUE: turned on FALSE: turned off | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | +------+----------+---------------+----------+------------------+-----------+-------+---------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+ 1 rows in setModify the
connection_control_max_connection_delayparameter of a specified tenant.obclient> SHOW PARAMETERS LIKE 'connection_control_max_connection_delay' TENANT = 'tenant'; +-------+----------+----------------+----------+-----------------------------------------+-----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+--------+---------+-------------------+ | zone | svr_type | svr_ip | svr_port | name | data_type | value | info | section | scope | source | edit_level | +-------+----------+----------------+----------+-----------------------------------------+-----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+--------+---------+-------------------+ | zone1 | observer | xx.xx.xx.218 | 2882 | connection_control_max_connection_delay | NULL | 2147483647 | The maximum delay in milliseconds for server response to failed connection attempts, if connection_control_failed_connections_threshold is greater than zero | OBSERVER | TENANT | DEFAULT | DYNAMIC_EFFECTIVE | +-------+----------+----------------+----------+-----------------------------------------+-----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+--------+---------+-------------------+ obclient> ALTER SYSTEM SET connection_control_max_connection_delay = 2147483646 TENANT = 'tenant'; obclient> SHOW PARAMETERS LIKE 'connection_control_max_connection_delay' TENANT = 'tenant'; +-------+----------+----------------+----------+-----------------------------------------+-----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+--------+---------+-------------------+ | zone | svr_type | svr_ip | svr_port | name | data_type | value | info | section | scope | source | edit_level | +-------+----------+----------------+----------+-----------------------------------------+-----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+--------+---------+-------------------+ | zone1 | observer | xx.xx.xx.218 | 2882 | connection_control_max_connection_delay | NULL | 2147483646 | The maximum delay in milliseconds for server response to failed connection attempts, if connection_control_failed_connections_threshold is greater than zero | OBSERVER | TENANT | DEFAULT | DYNAMIC_EFFECTIVE | +-------+----------+----------------+----------+-----------------------------------------+-----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+--------+---------+-------------------+