Drop a tenant|V4.2.0| docs|Distributed Database

Drop a tenant

Last Updated：2023-10-31 11:17:10 Updated

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 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 moves the specified tenant to the recycle bin. The sys tenant can purge or restore tenants in the recycle bin. For more information, see Overview.
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

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

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

Access the database named oceanbase.
```
obclient [(none)]> USE oceanbase;
```

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 | 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.

(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. Here 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
```
The parameters are described as follows:
- OBJECT_NAME indicates the name of an object in the recycle bin.
- ORIGINAL_NAME indicates the original name of the object.
- TYPE indicates 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 indicates the time when the object was placed in the recycle bin.
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.

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

(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 recycle bin parameters, see Recycle bin.

References

For information about how to restore a dropped tenant, visit the following links: