Step-by-Step Guide to Deleting a Tenant in OceanBase

Background

In OceanBase, a tenant is equivalent to a database cluster that provides database services to external users. Deleting a tenant removes all objects under it, such as databases and tables. Because data is extremely important, you may need to apply multiple strategies to confirm and handle exceptional scenarios before deletion, such as verifying business impact and ensuring the tenant can be restored if needed.

Environment Description

OB version: 5.7.25‑OceanBase‑v3.2.3.2 Tenant type: MySQL tenant Tenant to be deleted: obcp_t1

General Deletion Process

1. Confirm whether the tenant is currently in use; if it is, communicate with the business to verify the need for deletion.

2. If the tenant is not in use, lock the tenant.

3. Terminate existing idle connections to prevent ongoing SQL execution.

4. Keep the tenant locked for N days while awaiting business feedback to ensure no impact.

5. After receiving confirmation that there is no impact, delete the tenant.

Operation Steps

Notes

All operations are performed using the root account under the sys tenant.

It is recommended to connect directly to the observer because the kill operation must be executed on the observer.

The session_id for kill is obtained from the oceanbase.__all_virtual_processlist table.

set @tenant_name='obcp_t1';

-- Determine whether the tenant is currently in use

select user,tenant,host,db,command,svr_ip,user_client_ip,
  trans_id,thread_id,total_time,info
from oceanbase.__all_virtual_processlist
where tenant=@tenant_name and command!='Sleep'
order by total_time desc ;

-- If the tenant has no active business sessions, lock the tenant

alter tenant obcp_t1 lock ; -- Lock is idempotent and can be executed repeatedly
select tenant_name,locked from __all_tenant ; -- 1 means locked, 0 means unlocked

-- Generate kill statements for the tenant's sessions

select concat('kill ',id,';') from oceanbase.__all_virtual_processlist
where tenant=@tenant_name;

-- Execute the generated kill statements on the observer to terminate existing connections

kill xxx;
.....

-- After N days, if the business reports no impact, proceed with tenant deletion.

-- (Optional for MySQL tenants) When deleting, move the tenant to the recycle bin so it can be recovered later.

set recyclebin=1; DROP TENANT obcp_t1 ;
show parameters like 'recyclebin_object_expire_time'; -- View automatic recycle‑bin cleanup time

-- Directly delete the tenant

drop tenant ${object_name} force ; -- Delete tenant from recycle bin; object_name can be obtained via SHOW RECYCLEBIN
drop tenant obcp_t1 force ; -- Direct deletion

Keywords

#DeleteTenant #tenant

Article Recommendations

OB Operations | Session ID in kill

OB Operations | tenant – Delete Tenant Command

About SQLE

AiKeSheng Open‑Source Community's SQLE is a database audit tool for users and administrators, supporting multi‑scenario review, standardized release processes, native MySQL audit, and extensible database types.

More information and community discussion can be found at the official QQ group: 637150065 .