ALTER RESOURCE TENANT

Purpose

You can use the ALTER RESOURCE TENANT statement to modify the UNIT_NUM attribute of a resource pool that has been allocated to a tenant.

To modify other attributes of a resource pool, you can use the ALTER RESOURCE POOL statement. For more information about the ALTER RESOURCE POOL statement, see ALTER RESOURCE POOL.

Limitations and considerations

• ALTER RESOURCE TENANT modifies the number of units in all resource pools of a tenant.

• Before you use the ALTER RESOURCE TENANT statement to scale out or in a tenant, you must set a load balancing strategy for the tenant. Otherwise, the scale-out or scale-in may fail. For more information about how to set a load balancing strategy for a tenant, see Modify the number of resource units for a tenant.

Required privileges

You can modify the attributes of a resource pool only as the root user of the sys tenant (namely root@sys).

Syntax

ALTER RESOURCE TENANT tenant_name UNIT_NUM [=] unit_num [ DELETE UNIT_GROUP = ( unit_group_id_list ) ];

Parameters

Examples

• Change the number of units in all resource pools of the tenant1 tenant to 3.

  obclient(root@sys)[oceanbase]> ALTER RESOURCE TENANT tenant1 UNIT_NUM = 3;
  

• Delete the unit group whose UNIT_GROUP_ID is 1006 for the tenant2 tenant.

  1. Execute the following statements in sequence to enable load balancing:

     obclient(root@sys)[oceanbase]> ALTER SYSTEM SET enable_rebalance = true TENANT = 'tenant2';
     

     obclient(root@sys)[oceanbase]> ALTER SYSTEM SET enable_transfer = true TENANT = 'tenant2';
     

  2. Query the TENANT_ID value of the tenant.

     obclient(root@sys)[oceanbase]> SELECT TENANT_ID,TENANT_NAME,TENANT_TYPE FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME = 'tenant2';
     

     The query result is as follows:

     +-----------+-------------+-------------+
     | TENANT_ID | TENANT_NAME | TENANT_TYPE |
     +-----------+-------------+-------------+
     |      1006 | tenant2     | USER        |
     +-----------+-------------+-------------+
     1 row in set
     

  3. Query the resource units of the tenant and the UNIT_GROUP_ID value of the unit group to be deleted.

     obclient(root@sys)[oceanbase]> SELECT UNIT_ID, TENANT_ID, STATUS, RESOURCE_POOL_ID, UNIT_GROUP_ID, ZONE, SVR_IP, SVR_PORT FROM oceanbase.DBA_OB_UNITS WHERE TENANT_ID = 1006;
     

     The query result is as follows:

     +---------+-----------+--------+------------------+---------------+-------+----------------+----------+
     | UNIT_ID | TENANT_ID | STATUS | RESOURCE_POOL_ID | UNIT_GROUP_ID | ZONE  | SVR_IP         | SVR_PORT |
     +---------+-----------+--------+------------------+---------------+-------+----------------+----------+
     |    1016 |      1006 | ACTIVE |             1003 |          1006 | zone1 | xxx.xx.xxx.194 |     2882 |
     |    1017 |      1006 | ACTIVE |             1003 |          1007 | zone1 | xxx.xx.xxx.198 |     2882 |
     |    1018 |      1006 | ACTIVE |             1003 |          1006 | zone2 | xxx.xx.xxx.192 |     2882 |
     |    1019 |      1006 | ACTIVE |             1003 |          1007 | zone2 | xxx.xx.xxx.196 |     2882 |
     |    1020 |      1006 | ACTIVE |             1003 |          1006 | zone3 | xxx.xx.xxx.204 |     2882 |
     |    1021 |      1006 | ACTIVE |             1003 |          1007 | zone3 | xxx.xx.xxx.197 |     2882 |
     +---------+-----------+--------+------------------+---------------+-------+----------------+----------+
     6 rows in set
     

  4. Perform a scale-in operation based on the obtained UNIT_GROUP_ID value.

     obclient(root@sys)[oceanbase]> ALTER RESOURCE TENANT tenant2 UNIT_NUM = 1 DELETE UNIT_GROUP = (1006);
     

  5. After the operation, query the DBA_OB_TENANT_JOBS view for the execution status of the job for decreasing the number of resource units.

     obclient(root@sys)[oceanbase]> SELECT * FROM oceanbase.DBA_OB_TENANT_JOBS WHERE JOB_TYPE='ALTER_RESOURCE_TENANT_UNIT_NUM' AND TENANT_ID=1006;
     

     The query result is as follows:

     +--------+--------------------------------+------------+-------------+----------+----------------------------+----------------------------+-----------+-----------------------------------------------------------------------+--------------------+----------------+-------------+
     | JOB_ID | JOB_TYPE                       | JOB_STATUS | RESULT_CODE | PROGRESS | START_TIME                 | MODIFY_TIME                | TENANT_ID | SQL_TEXT                                                              | EXTRA_INFO         | RS_SVR_IP      | RS_SVR_PORT |
     +--------+--------------------------------+------------+-------------+----------+----------------------------+----------------------------+-----------+-----------------------------------------------------------------------+--------------------+----------------+-------------+
     |      6 | ALTER_RESOURCE_TENANT_UNIT_NUM | SUCCESS    |           0 |      100 | 2023-09-25 18:01:30.069506 | 2023-09-25 18:01:45.068740 |      1006 | ALTER RESOURCE TENANT tenant2 UNIT_NUM = 2                            | FROM: '1', TO: '2' | xxx.xx.xxx.196 |        2882 |
     |      7 | ALTER_RESOURCE_TENANT_UNIT_NUM | SUCCESS    |           0 |      100 | 2023-09-26 09:37:32.367076 | 2023-09-26 09:39:05.651200 |      1006 | ALTER RESOURCE TENANT tenant2 UNIT_NUM = 1 DELETE UNIT_GROUP = (1006) | FROM: '2', TO: '1' | xxx.xx.xxx.196 |        2882 |
     +--------+--------------------------------+------------+-------------+----------+----------------------------+----------------------------+-----------+-----------------------------------------------------------------------+--------------------+----------------+-------------+
     2 rows in set
     

References

• Modify the number of resource units for a tenant

• ALTER TENANT