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 dropped. However, the resource pools used by the tenant will not be dropped and can continue to be used by other tenants.

• If the recycle bin feature is enabled for the sys tenant, the DROP TENANT statement moves the specified tenant to the recycle bin. You can purge or restore tenants in the recycle bin from the sys tenant. For more information, see Overview.

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

Procedure

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

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

2. Access the database named oceanbase.

   obclient [(none)]> USE oceanbase;
   

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

   For example, to query the 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 | TENANT_ROLE | SWITCHOVER_STATUS | SWITCHOVER_EPOCH | SYNC_SCN            | REPLAYABLE_SCN      | READABLE_SCN        | RECOVERY_UNTIL_SCN  | LOG_MODE     | ARBITRATION_SERVICE_STATUS |
   +-----------+-------------+-------------+----------------------------+----------------------------+--------------+------------------------------+-------------------+--------------------+--------+---------------+--------+-------------+-------------------+------------------+---------------------+---------------------+---------------------+---------------------+--------------+----------------------------+
   |      1036 | mq_t1       | USER        | 2023-05-18 15:48:57.447657 | 2023-05-18 15:49:12.857944 | zone1;zone2  | FULL{1}@zone1, FULL{1}@zone2 | NULL              | MYSQL              | NORMAL | NO            | NO     | PRIMARY     | NORMAL            |                0 | 1684396167132057328 | 1684396167132057328 | 1684396167051160964 | 4611686018427387903 | NOARCHIVELOG | DISABLED                   |
   +-----------+-------------+-------------+----------------------------+----------------------------+--------------+------------------------------+-------------------+--------------------+--------+---------------+--------+-------------+-------------------+------------------+---------------------+---------------------+---------------------+---------------------+--------------+----------------------------+
   1 row in set
   

   For more information about the DBA_OB_TENANTS view, see DBA_OB_TENANTS.

4. (Optional) Enable the recycle bin feature as needed. OceanBase Database provides the tenant-level recycle bin feature to ensure the recoverability of data that is lost due to malicious operations or misoperations. The recycle bin feature is disabled by default. You can enable it for a tenant as needed. For more information about the recycle bin, see Overview.

   After enabling the recycle bin feature for a tenant, you can execute the SHOW RECYCLEBIN statement in the sys tenant to view the objects in the recycle bin of the sys tenant. Here is an example:

   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
   

   The parameters are described as follows:

   • 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, omysql 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 the 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 check whether the tenant is dropped to 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 recycle bin parameters, see recyclebin.

References

For information about how to restore a dropped tenant, see the following topics:

• Restore a tenant

• Recycle bin

• Restore objects from the recycle bin