Managing Tenant Deletion in OceanBase: Commands, Recycle Bin, and Delayed Deletion

Introduction: Deleting a tenant also removes its databases and tables, while the tenant's resource configuration remains and can be reused by other tenants.

Note: Only the root user of the sys tenant can execute the DROP TENANT command.

Syntax:

DROP TENANT [IF EXISTS] tenant_name [PURGE|FORCE];

Methods to delete a tenant:

1. Use the recycle bin (default): the tenant is moved to the recycle bin.

2. Immediate deletion: use DROP TENANT ... FORCE or PURGE to bypass the recycle bin.

Viewing and setting recycle‑bin retention time:

show parameters like 'schema_history_expire_time'; -- range [1h, 30d], default 7 days
alter system set schema_history_expire_time='7d'; -- takes effect immediately

Viewing and setting automatic recycle‑bin cleanup time:

show parameters like 'recyclebin_object_expire_time'; -- range [0s, +∞), 0s disables auto‑cleanup
ALTER SYSTEM SET recyclebin_object_expire_time = "7d"; -- takes effect immediately

Recycle‑bin operations for tenants:

set recyclebin=1; DROP TENANT t1; -- move tenant to recycle bin

show recyclebin; -- list tenants in recycle bin
select tenant_name,status,in_recyclebin, from_unixtime(substr(drop_tenant_time,1,10),"%Y-%m-%d %H:%i:%s") drop_tenant_time from oceanbase.__all_tenant where in_recyclebin=1;

FLASHBACK TENANT t1 TO BEFORE DROP; -- restore using original name
FLASHBACK TENANT __recycle_$_1665918035_1676612471384576 TO BEFORE DROP; -- restore using recycle‑bin object name

purge tenant t1; -- delete from recycle bin (delayed deletion, resources not released)
purge tenant __recycle_$_1665918035_1676617637326848; -- delete using object name
DROP TENANT __recycle_$_1665918035_1676613654350848 FORCE; -- permanently drop and release resources

Checking permanent deletion:

show recyclebin;
select tenant_name,status,in_recyclebin, from_unixtime(substr(drop_tenant_time,1,10),"%Y-%m-%d %H:%i:%s") drop_tenant_time from oceanbase.__all_tenant;

Delayed deletion of tenants (two ways):

set recyclebin=0; DROP TENANT t2; -- delayed deletion without recycle bin
DROP TENANT t2 PURGE; -- delayed deletion with PURGE

To view delayed‑deletion tenants:

select tenant_name,status,in_recyclebin, from_unixtime(substr(drop_tenant_time,1,10),"%Y-%m-%d %H:%i:%s") drop_tenant_time from oceanbase.__all_tenant where status='TENANT_STATUS_DROPPING';

Manually delete a delayed‑deletion tenant:

drop tenant t2 force;

Immediate (permanent) deletion:

drop tenant t3 force;

Tenant status explanations:

TENANT_STATUS_NORMAL : normal tenant.

in_recyclebin = 1 : tenant is currently in the recycle bin.

TENANT_STATUS_DROPPING with name starting _recycle$ : tenant has been purged from recycle bin and is in delayed deletion.

TENANT_STATUS_DROPPING without _recycle$ : tenant was dropped with PURGE (delayed deletion).

The article also includes a series of screenshots demonstrating environment preparation, tenant recovery, deletion from recycle bin, delayed deletion, and immediate deletion.