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.

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

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. Existing connections are not 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

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

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

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

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

  Assuming that the tenant has three zones: zone1, zone2, and zone3, the preceding statement is equivalent to the following statement:

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

• Modifying a resource pool is typically used for scenarios where you want to scale the number of replicas of a tenant up or down. However, when you modify a resource pool, you can only add or delete one resource pool at a time. You cannot directly replace the resource pool pool1 of a tenant with pool2 by executing the following statement. Otherwise, an error is returned.

  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 a tenant, such as by scaling it out, 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. In this example, F indicates a full-featured replica, and B_4 is the name of the newly added zone.

  Assume that 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 tenant1 tenant.

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

  4. Modify the locality of the tenant1 tenant.

     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 locality of the tenant1 tenant is as follows:

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

     Notice

     • When you modify the locality of a tenant, you can only add or remove columnstore replicas. You cannot convert the replica type. That is, if you want to add a columnstore replica, there must be no replica in the original zone. If you want to remove a columnstore replica, you must change the zone that contains the columnstore replica to a zone with no replicas.
     • After you add a columnstore replica to a tenant, you need to deploy an ODP and perform a series of configurations to route the connection to the columnstore replica. For more information about columnstore replicas, see Columnstore replicas.

• If the arbitration service is deployed, enable the arbitration service for the tenant1 tenant.

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

• Rename the tenant1 tenant to tenant101.

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

• Lock the tenant1 tenant.

  obclient [oceanbase]> ALTER TENANT tenant1 LOCK;