Delete a resource pool

OceanBase Database allows you to execute SQL statements to delete unused resource pools.

Prerequisites

Before you delete a resource pool, make sure that the resource pool is not being used by any tenant. If the resource pool is being used by a tenant, remove it from the tenant before deleting it. For information about how to remove a resource pool from a tenant, see Remove a resource pool from a tenant.

Delete a resource pool

The syntax for deleting a resource pool is as follows:

obclient> DROP RESOURCE POOL pool_name;

This statement can be executed only by the administrator of the sys tenant.

Example:

1. Log on to the sys tenant of the cluster as the root user.

2. Execute the following statement to check whether the to-be-deleted resource pool resource_pool1 is being used by a tenant:

   obclient> SELECT tenant_id,tenant_name FROM oceanbase.gv$unit WHERE resource_pool_name='resource_pool1';
   +-----------+-------------+
   | tenant_id | tenant_name |
   +-----------+-------------+
   |      NULL | NULL        |
   |      NULL | NULL        |
   +-----------+-------------+
   2 rows in set
   

   The query result shows that the resource pool is not being used by any tenant.

3. Execute the following statement to delete the resource pool:

   obclient> DROP RESOURCE POOL resource_pool1;
   Query OK, 0 rows affected
   

4. Execute the following statement to check whether the resource pool is deleted:

   obclient> SELECT * FROM oceanbase.__all_resource_pool;
   +----------------------------+----------------------------+------------------+------------------------+------------+----------------+-------------+-----------+--------------+--------------------+
   | gmt_create                 | gmt_modified               | resource_pool_id | name                   | unit_count | unit_config_id | zone_list   | tenant_id | replica_type | is_tenant_sys_pool |
   +----------------------------+----------------------------+------------------+------------------------+------------+----------------+-------------+-----------+--------------+--------------------+
   | 2021-11-11 14:29:24.849909 | 2021-11-11 14:29:24.854242 |                1 | sys_pool               |          1 |              1 | zone1       |         1 |            0 |                  0 |
   | 2021-11-11 14:43:01.455415 | 2021-11-11 14:43:01.466884 |             1002 | pool_Oracle_zone1_rhf  |          1 |           1002 | zone1       |      1002 |            0 |                  0 |
   | 2021-11-15 11:25:48.384105 | 2021-11-15 11:25:48.406186 |             1003 | pool_MySQL_zone1_zlz   |          1 |           1003 | zone1       |      1003 |            0 |                  0 |
   | 2021-11-15 11:26:38.986014 | 2021-11-15 11:26:38.997594 |             1004 | pool_MySQL1_zone1_cey  |          1 |           1004 | zone1       |      1004 |            0 |                  0 |
   | 2021-11-15 11:27:42.454288 | 2021-11-15 11:27:42.467915 |             1006 | pool_Oracle1_zone1_dfl |          1 |           1006 | zone1       |      1006 |            0 |                  0 |
   | 2021-11-17 14:38:03.945051 | 2021-11-17 14:38:03.945051 |             1022 | zyc_1117               |          1 |           1023 | zone1;zone2 |        -1 |            0 |                  0 |
   +----------------------------+----------------------------+------------------+------------------------+------------+----------------+-------------+-----------+--------------+--------------------+
   6 rows in set
   

   The query results do not contain resource_pool1, which indicates that the resource pool is deleted.