Drop a tenant

OceanBase Database allows you to use the DROP TENANT statement to drop a tenant.

After a tenant is dropped, the databases and tables of the tenant are also deleted. However, the resource pool used by the tenant is not deleted. It can be used by other tenants.

• If the recycle bin feature is enabled in the sys tenant, DROP TENANT drops the specified tenant to the recycle bin. The sys tenant can purge or restore tenants in the recycle bin. For more information, see Overview of recycle bin.

• If the recycle bin feature is disabled in the sys tenant, DROP TENANT directly drops the tenant as well as the databases and tables in this tenant.

Procedure

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

   obclient -h172.30.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. Execute the following statement to go to the database named oceanbase:

   obclient [(none)]> USE oceanbase;
   

3. Query the DBA_OB_TENANTS view for information about a tenant. Verify the tenant to be deleted based on the tenant ID, creation time, and resource information.

   To query information about the mq_t1 tenant, execute the following statement:

   obclient [oceanbase]> SELECT * FROM DBA_OB_TENANTS WHERE TENANT_NAME = 'mq_t1';
   +-----------+-------------+-------------+----------------------------+----------------------------+--------------+---------------------------------------------+-------------------+--------------------+--------+---------------+--------+
   | TENANT_ID | TENANT_NAME | TENANT_TYPE | CREATE_TIME                | MODIFY_TIME                | PRIMARY_ZONE | LOCALITY                                    | PREVIOUS_LOCALITY | COMPATIBILITY_MODE | STATUS | IN_RECYCLEBIN | LOCKED |
   +-----------+-------------+-------------+----------------------------+----------------------------+--------------+---------------------------------------------+-------------------+--------------------+--------+---------------+--------+
   |      1036 | mq_t1       | USER        | 2023-01-10 22:44:59.788717 | 2023-01-11 00:19:32.700572 | zone1,zone2  | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 | NULL              | MYSQL              | NORMAL | NO            | NO     |
   +-----------+-------------+-------------+----------------------------+----------------------------+--------------+---------------------------------------------+-------------------+--------------------+--------+---------------+--------+
   1 row in set
   

   For more information about the DBA_OB_TENANTS view, see oceanbase.DBA_OB_TENANTS.

4. (Optional) OceanBase Database provides the tenant-level recycle bin feature to ensure the recoverability of data that is lost due to malicious operations or misoperations. By default, the recycle bin is enabled. You can use the SHOW RECYCLEBIN statement to view the objects in the recycle bin of the current sys tenant. Examples:

   For more information about the recycle bin, see Recycle bin.

   obclient [oceanbase]> SHOW RECYCLEBIN;
   +--------------------------------+----------------+----------+----------------------------+
   | OBJECT_NAME                    | ORIGINAL_NAME  | TYPE     | CREATETIME                 |
   +--------------------------------+----------------+----------+----------------------------+
   | __recycle_$_1_1600136460199936 | obdemo_table    | TABLE    | 2020-09-15 10:21:00.207886 |
   | __recycle_$_1_1600136479664128 | obdemo_database | DATABASE | 2020-09-15 10:21:19.664534 |
   | __recycle_$_1_1600135793000960 | omysql          | TENANT   | 2020-09-15 10:23:25.773877 |
   +--------------------------------+----------------+----------+----------------------------+
   3 rows in set
   

   Parameters:

   • OBJECT_NAME: the name of an object in the recycle bin.
   • ORIGINAL_NAME: the original name of the object.
   • TYPE: the type of the object in the recycle bin. Valid values: INDEX, TABLE, DATABASE, and TENANT. In this example, mysql is a tenant object in the recycle bin.
   • CREATETIME: the time when the object was placed in the recycle bin.

5. Execute the DROP TENANT statement to drop a tenant.

   obclient> DROP TENANT mq_t1;
   

   For more information about the DROP TENANT statement, see DROP TENANT.

6. Query the DBA_OB_TENANTS view to verify whether the tenant is successfully dropped.

   obclient [oceanbase]> SELECT * FROM DBA_OB_TENANTS WHERE TENANT_NAME = 'mq_t1';
   Empty set
   

7. (Optional) If the recycle bin feature is enabled in step 4, you can execute the SHOW RECYCLEBIN statement to view the objects in the recycle bin.

   obclient [oceanbase]> SHOW RECYCLEBIN;
   +-------------------------------------+---------------+--------+----------------------------+
   | OBJECT_NAME                         | ORIGINAL_NAME | TYPE  | CREATETIME                   |
   +-------------------------------------+---------------+--------+----------------------------+
   | __recycle_$_100017_1673367572701376 | mq_t1         | TENANT | 2023-01-11 00:23:36.317242 |
   +-------------------------------------+---------------+--------+----------------------------+
   1 row in set
   

   For more information about parameters related to the recycle bin, see recyclebin.

More information

If you want to restore a dropped tenant, visit the following links:

• Restore a tenant

• Recycle bin

• Restore objects from the recycle bin