CREATE TENANT|V4.4.2| docs|Distributed Database

CREATE TENANT

Last Updated：2026-03-06 07:02:42 Updated

Purpose

The CREATE TENANT statement is used to create a user tenant. OceanBase Database only supports creating user tenants. The system tenant is automatically created when the cluster is created. The Meta tenant is an internally managed tenant of OceanBase Database. Each user tenant corresponds to a Meta tenant, and the lifecycle of the Meta tenant is consistent with that of the user tenant.

The CREATE TENANT statement creates a tenant with the default tenant role of PRIMARY.

Limitations and considerations

OceanBase Database supports two types of tenants: MySQL compatible mode and Oracle compatible mode. When creating a tenant, you must specify the appropriate type. After a tenant is created, its type cannot be modified.
When creating a tenant, you must allocate a resource pool to it. Therefore, you must plan resources in advance and create the resource pool. For more information about how to create a resource pool, see Create a tenant.
For more information about the limitations on cloning a tenant, see Clone a tenant.

Privilege requirements

Only the root user of the sys tenant (root@sys) can create a tenant. Other tenants cannot create tenants.

Syntax

The syntax for creating a tenant is as follows:

CREATE TENANT { {[IF NOT EXISTS] tenant_name [tenant_option_list] [set_sys_var]} | {clone_tenant_clause} };

tenant_option_list:
    tenant_option [, tenant_option...]

tenant_option:
      LOCALITY [=] 'locality_description'
    | PRIMARY_ZONE [=] primary_zone_name
    | RESOURCE_POOL_LIST [=] (pool_name [, pool_name...])
    | ENABLE_ARBITRATION_SERVICE [=] {True | False}
    | {CHARACTER SET | CHARSET} [=] charset_name
    | COLLATE [=] collation_name
    | COMMENT [=] 'string'

set_sys_var:
    {SET | SET VARIABLES | VARIABLES} var_name {TO | =} var_value [,var_name {TO | =} var_value...]

clone_tenant_clause:
    new_tenant_name FROM source_tenant_name WITH
      RESOURCE_POOL [=] resource_pool_name,
      UNIT [=] unit_config

Parameters

Parameter	Description
tenant_name	The name of the tenant. It can be up to 63 bytes in length and must start with a letter or an underscore. It can contain only uppercase and lowercase letters, digits, and underscores. It cannot be a keyword of OceanBase Database.
IF NOT EXISTS	If the tenant to be created already exists and the `IF NOT EXISTS` option is not specified, an error will be returned.
LOCALITY	Describes the distribution of replicas across zones. The supported replica types in the current version are as follows: Full-featured replica: named FULL, abbreviated as F. For more information, see Full-featured replica. Read-only replica: named READONLY, abbreviated as R. For more information, see Read-only replica. Columnstore replica: named COLUMNSTORE, abbreviated as C. For more information, see Columnstore replica. Example: `LOCALITY = 'F@z1,F@z2,F@z3'`, `LOCALITY = 'F@z1,R@z2,C@z3'`, or `LOCALITY = 'F@z1,R@z2,COLUMNSTORE@z3'`
PRIMARY_ZONE	The primary zone of the tenant. The supported values are as follows: `RANDOM`: specifies that the primary zone of the tenant is all zones where the tenant is located, indicating that all zones have the same priority. zone_name: specifies that the primary zone of the tenant is a specific zone. If the primary zone is not specified when creating a tenant, the default value is `RANDOM`.
RESOURCE_POOL_LIST	The list of resource pools allocated to the tenant. This parameter is required when creating a tenant. If multiple resource pools are specified, the `UNIT_NUM` values of these resource pools must be the same. Notice The `ZONE_LIST` values of multiple resource pools for the same tenant must not overlap.
ENABLE_ARBITRATION_SERVICE	Specifies whether to enable the arbitration service for the tenant. The default value is `False` if this option is not specified. In a cluster with the arbitration service, if the arbitration service is not enabled when creating a tenant, you can enable it after the tenant is created. For more information, see Enable the arbitration service for a tenant.
CHARACTER SET \| CHARSET	The character set for the tenant. For more information, see Character set (MySQL-compatible mode) and Character set (Oracle-compatible mode).
COLLATE	The collation for the tenant in MySQL-compatible mode. For more information, see Collation (MySQL-compatible mode). Note When creating a tenant in Oracle-compatible mode, you cannot specify the collation for the tenant. If you specify a collation, the syntax will not return an error, but the setting will be overridden by the default collation corresponding to the character set.
set_sys_var	The value of the system variable for the tenant. For more information about `var_name`, see var_name.
COMMENT	The comment for the tenant, which is optional.
clone_tenant_clause	The clause for cloning a tenant.
new_tenant_name	The name of the new cloned tenant.
source_tenant_name	The name of the source tenant, which is the tenant to be cloned.
resource_pool_name	The name of the resource pool. When a tenant is cloned, resource pools will be automatically created for the new tenant based on the resource distribution of the source tenant.
unit_config	The unit specification for the resource pools of the new cloned tenant.

var_name

var_name can be set to the following values:

ob_compatibility_mode: specifies the compatibility mode of the tenant (MySQL or Oracle-compatible mode). This parameter can only be set when the tenant is created. If you do not set this parameter, the default compatibility mode is MySQL.
ob_tcp_invited_nodes: specifies the IP addresses of the clients that are allowed to connect to the tenant. In the example, % indicates that all clients are allowed to connect to the tenant. If you do not set this parameter, only the IP address of the local host is allowed to connect to the tenant.
lower_case_table_names: specifies whether the database is case-sensitive. This parameter can only be set when the tenant is created, and only takes effect in MySQL-compatible mode. After the tenant is created, you cannot modify this parameter by executing an SQL statement.
ob_compatibility_control: specifies the behavior of a feature that is compatible with both MySQL 5.7 and MySQL 8.0. The value can be MYSQL5.7 or MYSQL8.0. If you do not set this parameter, the default value is MYSQL5.7.
read_only: specifies whether the tenant is in read-only mode or read/write mode. If you do not set this parameter, the default value is read/write mode.

Examples

Create a MySQL-compatible tenant named test_tenant with the primary zone zone1;zone2,zone3, resource pool pool1, and character set utf8mb4, allowing all client IP addresses to connect to the database.
```
obclient [oceanbase]>  CREATE TENANT IF NOT EXISTS test_tenant PRIMARY_ZONE='zone1;zone2,zone3', RESOURCE_POOL_LIST=('pool1'), CHARSET='utf8mb4' SET ob_tcp_invited_nodes TO '%';
```
Create a MySQL-compatible tenant named tenant_c with the locality F@zone1,F@zone2,C@zone3, primary zone zone1;zone2,zone3, resource pool pool1, and allowing all client IP addresses to connect to the database.

Notice

After you create a tenant that contains columnstore replicas, you must deploy ODP and configure it to route connections to the columnstore replicas. For more information, see Columnstore replicas.
```
obclient [oceanbase]> CREATE TENANT tenant_c LOCALITY = 'F@zone1,F@zone2,C@zone3', primary_zone='zone1;zone2,zone3', RESOURCE_POOL_LIST=('pool1') SET ob_tcp_invited_nodes = '%';
```

Create an Oracle-compatible tenant named tenant1 with the primary zone zone1 and resource pool pool1, allowing all client IP addresses to connect to the database.

obclient [oceanbase]>  CREATE TENANT tenant1 PRIMARY_ZONE='zone1', RESOURCE_POOL_LIST=('pool1') SET ob_compatibility_mode='oracle',  ob_tcp_invited_nodes='%';

Clone a tenant named clone_mysql001 from the standby tenant mysql001. The new tenant is assigned to the resource pool clone_mysql001_resource_pool and the resource unit unit001.

Notice

Before you clone a tenant, you must enable log archiving for the source tenant. During the execution of the tenant cloning statement, you cannot disable log archiving for the source tenant. For more information, see Overview of log archiving.

CREATE TENANT clone_mysql001 FROM mysql001
      WITH
      RESOURCE_POOL = clone_mysql001_resource_pool,
      UNIT = unit001;

When you execute the tenant cloning statement in the sys tenant, the statement generates a corresponding cloning task. You can query the DBA_OB_CLONE_PROGRESS and DBA_OB_CLONE_HISTORY views to view the execution status of the cloning task.

Here is an example:

obclient [(none)]> SELECT * FROM oceanbase.DBA_OB_CLONE_HISTORY WHERE CLONE_TENANT_NAME = 'clone_mysql001'\G

The return result is as follows:

*************************** 1. row ***************************
        CLONE_JOB_ID: 1706248530701634201
            TRACE_ID: YB42AC1E87C9-00060F1AD6DF0264-0-0
    SOURCE_TENANT_ID: 1002
  SOURCE_TENANT_NAME: mysql001
    CLONE_TENANT_ID: 1012
  CLONE_TENANT_NAME: clone_mysql001
  TENANT_SNAPSHOT_ID: 1706248530759745885
TENANT_SNAPSHOT_NAME: _inner_snapshot$1706248530759740679
    RESOURCE_POOL_ID: 1006
  RESOURCE_POOL_NAME: clone_mysql001_resource_pool
    UNIT_CONFIG_NAME: unit001
        RESTORE_SCN: 1706248531829799503
              STATUS: CLONE_SYS_SUCCESS
      CLONE_JOB_TYPE: FORK
    CLONE_START_TIME: 2024-01-26 13:55:30.705699
CLONE_FINISHED_TIME: 2024-01-26 13:56:00.141434
            RET_CODE: 0
      ERROR_MESSAGE: NULL
1 row in set

For more information about the fields in the views, see DBA_OB_CLONE_PROGRESS and DBA_OB_CLONE_HISTORY.

CREATE TENANT

Purpose

Limitations and considerations

Privilege requirements

Syntax

Parameters

Notice

Note

var_name

Examples

Notice

Notice

References