OceanBase Tenant Deletion Methods and Syntax

The author, a senior database expert with over ten years of MySQL experience, introduces OceanBase's tenant deletion design, which offers three methods: normal deletion (subject to the sys tenant recycle bin), delayed deletion (retains data for a configurable period), and immediate deletion (permanent removal).

The deletion syntax is DROP TENANT [IF EXISTS] tenant_name [PURGE|FORCE]; . When the recycle bin is enabled, a normal DROP TENANT moves the tenant to the recycle bin, allowing later restoration. If the recycle bin is disabled, the tenant is removed but its data remains accessible for a period defined by schema_history_expire_time , after which a background garbage‑collection thread permanently deletes it.

DROP TENANT PURGE performs a delayed deletion regardless of the recycle‑bin setting, while DROP TENANT FORCE deletes the tenant instantly.

Example workflow:

create resource unit mini1 max_cpu 1, max_memory '1G', max_disk_size '1G', max_session_num 1200, max_iops 1000;
create resource pool p3 unit 'mini1', unit_num=1;
create resource pool p4 unit 'mini1', unit_num=1;
create tenant tenant1 resource_pool_list=('p3');
create tenant tenant2 resource_pool_list=('p4');

With the recycle bin turned on , executing drop tenant tenant1; moves tenant1 to the recycle bin, which can be listed via show recyclebin; and restored using flashback tenant tenant1 to before drop; .

set recyclebin=off;
drop tenant tenant1;
-- tenant1 remains visible in __all_tenant and can still run DML until schema_history_expire_time expires.

For delayed deletion, drop tenant tenant2 purge; keeps the tenant data until the configured expiration time, after which it is removed. For immediate removal, drop tenant tenant2 force; deletes the tenant at once, and subsequent queries to __all_tenant return no rows.

These commands demonstrate how OceanBase manages tenant lifecycle, offering flexibility for accidental deletions, lease expirations, and immediate cleanup scenarios.