Drop a tenant

OceanBase Database supports dropping a tenant using the DROP TENANT statement.

After a tenant is dropped, the databases and tables under 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 will move the specified tenant to the recycle bin. For tenants in the recycle bin, the sys tenant can purge or recover them from the recycle bin later using the tenant-level recycle bin feature. For more information about the recycle bin feature, see Overview.

• If the recycle bin feature is disabled for the sys tenant, the DROP TENANT statement will directly drop the tenant along with its databases and tables.

Procedure

1. Log in to the sys tenant of the cluster using 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 tenant information through the DBA_OB_TENANTS view. Identify the tenant to be dropped based on its ID, creation time, and resource allocation information, etc. For example, to query information about the mq_t1 tenant:

   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) To prevent irreversible data loss caused by malicious or accidental operations, OceanBase Database provides the tenant-level recycle bin feature, which supports quick recovery of tenant-level data. The recycle bin feature is enabled by default. You can use the SHOW RECYCLEBIN statement under the current system tenant to view the objects in the system tenant's recycle bin. Below is an example.

   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
   

   where

   • OBJECT_NAME: Indicates the name of the object in the recycle bin.
   • ORIGINAL_NAME: Indicates the original name of the object.
   • TYPE: Indicates the type of object in the recycle bin. Currently, it supports four types: INDEX, TABLE, DATABASE, and TENANT. In this example, omysql is a TENANT-type object in the recycle bin.
   • CREATETIME: Indicates the time when the object was moved to 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 confirm that the tenant has been 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 information about 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-related parameters, see Recycle bin.

References

If you need to recover a dropped tenant, see the following topics:

• Restore a tenant

• Recycle bin

• Restore objects from the recycle bin