ALTER TABLEGROUP|V5.0.1|OceanBase Database|docs|OceanBase Documentations

ALTER TABLEGROUP

Last Updated：2026-09-04 11:28:26 Updated

Purpose

This statement is used to add multiple tables to a table group and modify the attributes of the table group.

Privilege requirements

To execute the ALTER TABLEGROUP statement, the current user must have the ALTER privilege. For more information about OceanBase Database privileges, see Privilege types in Oracle-compatible mode.

Limitations and considerations

During the upgrade of OceanBase Database, modifying table group attributes is prohibited.
For V5.x versions, starting from V5.0.1, the distribution of partitions within a table group is determined jointly by the SHARDING and SCOPE attributes. The current version only supports certain combinations of the SHARDING and SCOPE attributes. Specifying an unsupported combination will result in an error. Supported combinations include:
- SHARDING = 'NONE' + SCOPE = 'SERVER'
- SHARDING = 'NONE' + SCOPE = 'ZONE'
- SHARDING = 'NONE' + SCOPE = 'CLUSTER'
- SHARDING = 'PARTITION' + SCOPE = 'CLUSTER'
- SHARDING = 'ADAPTIVE' + SCOPE = 'CLUSTER'
- SHARDING = 'SUBPARTITION' + SCOPE = 'CLUSTER'

Limitations and considerations

During the upgrade of OceanBase Database, modifying the properties of a table group is prohibited.
For V5.x versions, starting from V5.0.1, the distribution of partitions within a table group is determined by both SHARDING and SCOPE. The current version only supports certain combinations of the SHARDING and SCOPE attributes. Specifying an unsupported combination will result in an error. Supported combinations include:
- SHARDING = 'NONE' + SCOPE = 'SERVER'
- SHARDING = 'NONE' + SCOPE = 'ZONE'
- SHARDING = 'NONE' + SCOPE = 'CLUSTER'
- SHARDING = 'PARTITION' + SCOPE = 'CLUSTER'
- SHARDING = 'ADAPTIVE' + SCOPE = 'CLUSTER'

Syntax

ALTER TABLEGROUP tablegroup_name
    {ADD [TABLE] table_name_list
    | [SET] [sharding_option,] [scope_option]
    };

table_name_list:
    table_name [, table_name ...]

sharding_option:
    SHARDING = '{NONE | PARTITION | ADAPTIVE | SUBPARTITION}'

scope_option:
    SCOPE = '{SERVER | ZONE | CLUSTER}'

Parameters

Parameter	Description
tablegroup_name	The name of the table group to be modified.
ADD [TABLE] table_name_list	This statement is used to add tables to a table group. When adding multiple tables to a table group, separate the table names with commas (','). Table names can be repeated when adding multiple tables. If a table to be added already belongs to the target table group, OceanBase Database does not report an error.
sharding_option	Optional. Specifies the value for the`SHARDING`Attribute.`SHARDING`It determines the aggregation method for partitions among tables within a table group. A group of aggregated partitions is called a partition group, which is the smallest unit of distribution and sharding. For more details, see sharding_option.
scope_option	Optional. Specifies the value for the`SCOPE`Attribute.`SCOPE`Determines the distribution range of all aggregated partition groups within the table group. For more information, see scope_option. Note For V5.x versions, the `SCOPE` attribute for table groups is supported starting from V5.0.1.

sharding_option

NONE: No restrictions on tables that can be added to the table group.
PARTITION: Indicates the partition of the table to be added to the table group. It must have the same partitioning method as all tables in the table group, including the partition type, number of partitions, and partition values.
ADAPTIVE: Indicates that the partitions of the tables added to the table group, including both primary and secondary partitions, must be consistent with those of all other tables in the group in terms of partition type, number of partitions, and partition values. The tables added to the table group must all be of the same type (either all primary partitioned tables or all subpartitioned tables).
SUBPARTITION: Indicates that the tables added to the table group must be subpartitioned tables. The partitioning methods for the primary partitions of different tables must be the same, and so must the subpartitioning methods, including the partitioning key type, partitioning function, number of partitions, and partition boundary values.

Note

For V5.x versions, the SHARDING = 'SUBPARTITION' attribute for table groups is supported starting from V5.0.1.

The same partitioning methods include:

The partition types are the same (for example, both are HASH+RANGE partitions).
For HASH partitioning, the number of referenced columns and the number of partitions must be the same.
For RANGE partitioning, the number of referenced columns, the number of partitions, and the RANGE partitioning points must be the same.

For subpartitions, the requirements are the same as those above, depending on their partition type.

scope_option

SERVER: Indicates that the leaders of all partition groups are located on the same node.
ZONE: Indicates that the leaders of all partition groups are distributed within the same zone and are scattered across nodes within that zone.
CLUSTER: Indicates that the leaders of all partition groups are scattered across nodes in the cluster.

Examples

Create a table group named tblgroup1 with its SHARDING attribute set to NONE, and add the partitioned tables tbl1 and tbl2 to this table group.

obclient> CREATE TABLEGROUP tblgroup1 SHARDING = 'NONE';
Query OK, 0 rows affected

obclient> CREATE TABLE tbl1(col1 NUMBER,col2 VARCHAR2(50))
    PARTITION BY LIST(col1) SUBPARTITION BY LIST (col2)
      SUBPARTITION TEMPLATE
      (SUBPARTITION mp0 VALUES ('01'),
       SUBPARTITION mp1 VALUES ('02'),
       SUBPARTITION mp2 VALUES ('03')
       )
      (PARTITION p0 VALUES('01'),
       PARTITION p1 VALUES('02')
      );
Query OK, 0 rows affected

obclient> CREATE TABLE tbl2(col1 NUMBER,col2 VARCHAR2(50)) TABLEGROUP = tblgroup1
    PARTITION BY LIST(col1) SUBPARTITION BY LIST(col2)
      SUBPARTITION TEMPLATE
      (SUBPARTITION mp0 VALUES('01'),
       SUBPARTITION mp1 VALUES('02'),
       SUBPARTITION mp2 VALUES('03')
      )
      (PARTITION p0 VALUES('01'),
       PARTITION p1 VALUES('02')
      );
Query OK, 0 rows affected

obclient> ALTER TABLEGROUP tblgroup1 ADD tbl1,tbl2;
Query OK, 0 rows affected

Since tbl1 and tbl2 have the same partitioning method, the SHARDING attribute can be changed to PARTITION or ADAPTIVE.

obclient> ALTER TABLEGROUP tblgroup1 SHARDING = 'PARTITION';
Query OK, 0 rows affected

obclient> ALTER TABLEGROUP tblgroup1 SHARDING = 'ADAPTIVE';
Query OK, 0 rows affected

Create a table group named tblgroup2 with its SHARDING attribute set to NONE. Add the non-partitioned table tbl1 and the partitioned table tbl2 to the table group.

obclient> CREATE TABLEGROUP tblgroup2 SHARDING = 'NONE';
Query OK, 0 rows affected

obclient> CREATE TABLE tbl1 (col INT);
Query OK, 0 rows affected

obclient> CREATE TABLE tbl2(col1 NUMBER,col2 VARCHAR2(50)) TABLEGROUP = tblgroup2
    PARTITION BY LIST(col1) SUBPARTITION BY LIST(col2)
      SUBPARTITION TEMPLATE
      (SUBPARTITION mp0 VALUES('01'),
       SUBPARTITION mp1 VALUES('02'),
       SUBPARTITION mp2 VALUES('03')
      )
      (PARTITION p0 VALUES('01'),
       PARTITION p1 VALUES('02')
      );
Query OK, 0 rows affected

obclient> ALTER TABLEGROUP tblgroup2 ADD tbl1,tbl2;
Query OK, 0 rows affected

Since the table group tblgroup2 contains both non-partitioned tables and partitioned tables, the SHARDING attribute cannot be changed to PARTITION or ADAPTIVE.

obclient> ALTER TABLEGROUP tblgroup2 SHARDING = 'PARTITION';
OBE-00600: internal error code, arguments: -4179, not all tables are non-partitioned or partitioned, modify tablegroup sharding attribute not allowed

obclient> ALTER TABLEGROUP tblgroup2 SHARDING = 'ADAPTIVE';
OBE-00600: internal error code, arguments: -4179, not all tables are non-partitioned or partitioned, modify tablegroup sharding attribute not allowed

Create a table group named tblgroup3 with its SHARDING attribute set to NONE, and add the partitioned tables tbl1 and tbl2 to this table group.

obclient> CREATE TABLEGROUP tblgroup3 SHARDING = 'NONE';
Query OK, 0 rows affected

obclient> CREATE TABLE tbl1(col1 NUMBER,col2 VARCHAR2(50))
   PARTITION BY LIST(col1) SUBPARTITION BY LIST (col2)
     SUBPARTITION TEMPLATE
     (SUBPARTITION mp0 VALUES ('01'),
      SUBPARTITION mp1 VALUES ('02')
      )
     (PARTITION p0 VALUES('01'),
      PARTITION p1 VALUES('02')
     );
Query OK, 0 rows affected

obclient> CREATE TABLE tbl2(col1 NUMBER,col2 VARCHAR2(50))
   PARTITION BY LIST(col1) SUBPARTITION BY LIST(col2)
     SUBPARTITION TEMPLATE
     (SUBPARTITION mp0 VALUES('01'),
      SUBPARTITION mp1 VALUES('02'),
      SUBPARTITION mp2 VALUES('03')
     )
     (PARTITION p0 VALUES('01'),
      PARTITION p1 VALUES('02')
     );
Query OK, 0 rows affected

obclient> ALTER TABLEGROUP tblgroup3 ADD tbl1,tbl2;
Query OK, 0 rows affected

Since the partition types of the partitions in tables tbl1 and tbl2 within table group tblgroup3 are the same, but the subpartition types are different (the number of subpartitions is different), the SHARDING attribute can be changed to PARTITION, but not to ADAPTIVE.

obclient> ALTER TABLEGROUP tblgroup3 SHARDING = 'PARTITION';
Query OK, 0 rows affected

obclient> ALTER TABLEGROUP tblgroup3 SHARDING = 'ADAPTIVE';
OBE-00600: internal error code, arguments: -4179, subpartition num not matched, modify tablegroup sharding attribute not allowed

Create a table group named tblgroup4 with its SHARDING attribute set to ADAPTIVE. Then, modify the number of partitions for tables tbl1 and tbl2 within the table group. At this point, all tables in the table group have inconsistent partitioning schemes, and no new table tbl3 can be added to the table group.

obclient> CREATE TABLEGROUP tblgroup4 SHARDING = 'ADAPTIVE';
Query OK, 0 rows affected

obclient> CREATE TABLE tbl1(col1 int,col2 int)
    PARTITION BY RANGE(col1) SUBPARTITION BY RANGE(col2)
      SUBPARTITION TEMPLATE
        (SUBPARTITION mp0 VALUES LESS THAN(2021),
         SUBPARTITION mp1 VALUES LESS THAN(2022),
         SUBPARTITION mp2 VALUES LESS THAN(2023)
        )
        (PARTITION p0 VALUES LESS THAN(100),
         PARTITION p1 VALUES LESS THAN(200)
        );
Query OK, 0 rows affected

obclient> CREATE TABLE tbl2(col1 int,col2 int)
    PARTITION BY RANGE(col1) SUBPARTITION BY RANGE(col2)
      SUBPARTITION TEMPLATE
       (SUBPARTITION mp0 VALUES LESS THAN(2021),
        SUBPARTITION mp1 VALUES LESS THAN(2022),
        SUBPARTITION mp2 VALUES LESS THAN(2023)
       )
      (PARTITION p0 VALUES LESS THAN(100),
       PARTITION p1 VALUES LESS THAN(200)
      );
Query OK, 0 rows affected

obclient> ALTER TABLEGROUP tblgroup4 ADD tbl1,tbl2;
Query OK, 0 rows affected

obclient> ALTER TABLE tbl1 ADD PARTITION p3 VALUES LESS THAN(400), PARTITION p4 VALUES LESS THAN(500);
Query OK, 0 rows affected

obclient> ALTER TABLE tbl2 DROP SUBPARTITION p0smp0,p0smp1;
Query OK, 0 rows affected

obclient> CREATE TABLE tbl3(col1 int,col2 int)
    PARTITION BY RANGE(col1) SUBPARTITION BY RANGE(col2)
      SUBPARTITION TEMPLATE
     (SUBPARTITION mp0 VALUES LESS THAN(2021),
      SUBPARTITION mp1 VALUES LESS THAN(2022),
      SUBPARTITION mp2 VALUES LESS THAN(2023)
     )
     (PARTITION p0 VALUES LESS THAN(100),
      PARTITION p1 VALUES LESS THAN(200)
      );
Query OK, 0 rows affected

obclient> ALTER TABLEGROUP tblgroup4 ADD tbl3;
OBE-00600: internal error code, arguments: -4179, partition num not equal, add table to tablegroup not allowed

Create a table group named tblgroup5 with its SHARDING attribute set to ADAPTIVE, and then change the SHARDING attribute of table group tblgroup5 to SUBPARTITION.
```
obclient> CREATE TABLEGROUP tblgroup5 SHARDING = 'ADAPTIVE';
```
```
obclient> ALTER TABLEGROUP tblgroup5 SET SHARDING = 'SUBPARTITION';
```

OceanBase

Customer Stories

Documentation

ALTER TABLEGROUP

Purpose

Privilege requirements

Limitations and considerations

Limitations and considerations

Syntax

Parameters

Note

sharding_option

Note

scope_option

Examples

References