ALTER TENANT

Purpose

The ALTER TENANT statement is used to modify the properties of a user tenant, including the locality, primary zone, resource pool list, and system variable values.

If you need to modify the name of a user tenant, you can use the ALTER TENANT ... RENAME statement.

If you need to lock or unlock a user tenant, you can use the ALTER TENANT ... LOCK / UNLOCK statement. After executing the ALTER TENANT ... LOCK statement, the system will no longer allow users to create new connections to the locked tenant, but existing connections will not be affected.

Privilege requirements

Only the root user of the sys tenant (root@sys) can modify a tenant. Other tenants do not support modifying tenants.

Syntax

ALTER TENANT

ALTER TENANT tenant_name
    [SET] [tenant_option_list] [set_sys_var];

tenant_option_list:
    tenant_option [, tenant_option ...]

tenant_option:
      LOCALITY [=] 'locality_description'
    | PRIMARY_ZONE [=] zone
    | RESOURCE_POOL_LIST [=](pool_name [, pool_name...])
    | ENABLE_ARBITRATION_SERVICE [=] {True | False}
    | [DEFAULT] TABLEGROUP [=] {NULL | tablegroup_name}
    | ENABLE_EXTENDED_ROWID [=] {true | false}
    | COMMENT [=]'string'

set_sys_var:
    VARIABLES var_name {TO | =} var_value [,var_name {TO | =} var_value...]

ALTER TENANT ... RENAME

ALTER TENANT tenant_name RENAME GLOBAL_NAME TO new_tenant_name;

ALTER TENANT ... LOCK / UNLOCK

ALTER TENANT tenant_name LOCK | UNLOCK;

Parameters

Examples

• Change the primary zone of the tenant1 tenant to zone2.

  obclient [oceanbase]> ALTER TENANT tenant1 primary_zone='zone2';
  

• Change the primary zone of the tenant1 tenant to RANDOM.

  obclient [oceanbase]> ALTER TENANT tenant1 primary_zone = RANDOM;
  

  Assuming the tenant has three zones named zone1, zone2, and zone3, this statement is equivalent to

  obclient [oceanbase]> ALTER TENANT tenant1 primary_zone = 'zon1,zone2,zone3';
  

• Modifying a resource pool is typically used in scenarios where you need to upgrade or downgrade the number of replicas for a tenant. However, when modifying a resource pool, you can only add or remove one resource pool at a time. You cannot directly replace the tenant's resource pool pool1 with pool2 using the following statement, or the following error will occur.

  obclient [oceanbase]> ALTER TENANT tenant1 resource_pool_list=('pool2');
  

  The error message is as follows:

  ERROR 1210 (HY000): Incorrect arguments to resource pool list
  

  If you want to adjust the available resources for the tenant, such as expanding the tenant, you can increase the specifications of the resource units.

  obclient [oceanbase]> ALTER RESOURCE UNIT unit1 MAX_CPU 15, MEMORY_SIZE '20G', MAX_IOPS 1280, MIN_CPU=10, MIN_IOPS=1024;
  

• Modify the locality of the tenant1 tenant to add more replicas. Here, F indicates a full-featured replica, and B_4 is the name of the new zone.

  Assuming the current locality of the tenant1 tenant is F@B_1,F@B_2,F@B_3, the resource pool is pool1, and the ZONE_LIST range is 'B_1','B_2','B_3'.

  1. Create a resource unit named unit2.

     obclient [oceanbase]> CREATE RESOURCE UNIT unit2, MAX_CPU=5, MIN_CPU=2.5, MEMORY_SIZE= '32G', MAX_IOPS=10000, MIN_IOPS=5000, LOG_DISK_SIZE=5301023539200;
     

  2. Create a resource pool named pool2.

     obclient [oceanbase]> CREATE RESOURCE POOL pool2 unit='unit2',unit_num=1,zone_list=('B_4');
     

  3. Add the pool2 resource pool to the tenant.

     obclient [oceanbase]> ALTER TENANT tenant1 resource_pool_list=('pool1','pool2');
     

  4. Modify the tenant's locality.

     obclient [oceanbase]> ALTER TENANT tenant1 LOCALITY='F@B_1,F@B_2,F@B_3,F@B_4';
     

     If the replica type to be added is a columnstore replica, the statement to modify the tenant's locality is as follows:

     obclient [oceanbase]> ALTER TENANT tenant1 LOCALITY = 'F@B_1,F@B_2,F@B_3,C@B_4';
     

     Notice

     • When modifying the locality of a tenant, for columnstore replicas, you can only add or remove replicas. You cannot convert the replica type. That is, if you want to add a columnstore replica, there must be no replicas in the original zone. If you want to remove a columnstore replica, you can only change the zone that contains the columnstore replica to a zone without any replicas.
     • After adding a columnstore replica to the tenant, you need to deploy an ODP and configure it to route connections to the columnstore replica. For more information about columnstore replicas, see Columnstore replicas.

• In a scenario where an arbitration service is deployed, enable the arbitration service for tenant1.

  obclient [oceanbase]> ALTER TENANT tenant1 ENABLE_ARBITRATION_SERVICE=true;
  

• Rename the ordinary tenant tenant1 to tenant101.

  obclient [oceanbase]> ALTER TENANT tenant1 RENAME GLOBAL_NAME TO tenant101;
  

• Lock the tenant1 tenant.

  obclient [oceanbase]> ALTER TENANT tenant1 LOCK;