OceanBase Single‑Node Deployment and Tenant Management Guide

The author, a DBA from the OceanBase delivery service team, introduces an original tutorial on deploying OceanBase in a single‑node (stand‑alone) environment and managing tenants, resources, and users.

1. First‑time tenant usage steps – a table outlines the five essential steps: create a resource unit, create a resource pool, create a tenant (MySQL type), create a user for the tenant, and grant the user appropriate permissions.

2. Creating the wms_tenant (MySQL type, three replicas)

create resource unit wms_unit1 max_cpu=5,min_cpu=2,memory_size='2G';
create resource pool wms_pool1 unit 'wms_unit1',unit_num 1;
CREATE TENANT IF NOT EXISTS wms_tenant charset='utf8mb4',replica_num=3, zone_list=('zone1','zone2','zone3'), primary_zone='RANDOM', comment 'mysql tenant/instance', resource_pool_list=('wms_pool1') set ob_tcp_invited_nodes='%',ob_compatibility_mode='mysql';

After creating the tenant, the author shows how to query the current resource‑unit configuration using SELECT ... FROM gv$ob_servers and SELECT * FROM oceanbase.DBA_OB_UNIT_CONFIGS , illustrating CPU and memory allocation.

3. Resource Unit Operations

3.1 View resource units

SELECT * FROM oceanbase.__all_unit_config;

3.2 Modify resource units (single or multiple attributes)

ALTER RESOURCE UNIT wms_unit1 MAX_CPU 8,MIN_CPU=3,MAX_IOPS=30000;
ALTER RESOURCE UNIT wms_unit1 MAX_CPU 5;

3.3 Delete resource units – the guide explains the difference between dropping an unused unit and attempting to drop a unit that is still referenced, and provides a workaround by creating a new unit and reassigning the pool before deletion.

4. Resource Pool Operations

create resource pool wms_pool2 unit 'wms_unit2',unit_num 1;
DROP RESOURCE POOL wms_pool2;

5. Tenant Operations

5.1 Create a MySQL‑type tenant (example test_tenant )

CREATE TENANT IF NOT EXISTS wms_tenant charset='utf8mb4',replica_num=3, zone_list=('zone1','zone2','zone3'), primary_zone='RANDOM', comment 'mysql tenant/instance', resource_pool_list=('wms_pool1') set ob_tcp_invited_nodes='%',ob_compatibility_mode='mysql';

5.2 Delete tenants – shows four variants (regular drop, drop with recycle‑bin, purge, and force) and explains their effects.

5.3 Switch tenants without leaving the system tenant

alter system change tenant wms_tenant;
SHOW TENANT;
alter system change tenant sys;

5.4 Modify tenant resources and locality, and note that changing the resource‑pool list is not supported.

5.5 View tenant variables, e.g., show variables like 'ob_tcp_invited_nodes';

6. Create User and Link to Tenant

CREATE USER 'user1'@'%' IDENTIFIED BY 'welcome1';
GRANT SELECT ON test.* TO user1;

Login as the new user to verify access and list databases.

7. Data Dictionary Reference

Database

Dictionary

Description

oceanbase

__all_unit_config

Resource unit allocation status

oceanbase

__all_virtual_server_stat

Virtual server resources, used for resource‑unit placement

oceanbase

__all_resource_pool

Resource pool information

oceanbase

__all_tenant

Base table for tenant information

oceanbase

gv$tenant

Tenant view based on __all_tenant

The article concludes with references to the SQLE open‑source project, documentation links, and community contact information.