ALTER TABLEGROUP|V4.4.2| docs|Distributed Database

ALTER TABLEGROUP

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

Purpose

This statement is used to add multiple tables to a table group and modify the properties 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 classification in Oracle-compatible mode.

Syntax

Add multiple tables to a table group.

ALTER TABLEGROUP tablegroup_name ADD [TABLE] table_name [, table_name...];

Modify the partitioning rules of the table group.

ALTER TABLEGROUP tablegroup_name tablegroup_option;

tablegroup_option:
    SHARDING = 'NONE' | 'PARTITION' | 'ADAPTIVE'

Parameters

Parameter Description

tablegroup_name The name of the table group. When adding multiple tables to a table group, separate the tables with commas (','). If multiple tables are added, duplicate table names are allowed. If the tables to be added already belong to the target table group, OceanBase Database does not return an error.

SHARDING

Parameter	Description
tablegroup_name	The name of the table group. When adding multiple tables to a table group, separate the tables with commas (','). If multiple tables are added, duplicate table names are allowed. If the tables to be added already belong to the target table group, OceanBase Database does not return an error.
SHARDING	The property of the table group. If the `SHARDING` property is not specified, the default value is `ADAPTIVE`. Valid values of `SHARDING` are as follows: `NONE`: No restrictions are imposed on the tables added to the table group. `PARTITION`: The primary partitions of the tables added to the table group must be consistent with the primary partitions of all tables in the table group. This includes the partitioning type, number of partitions, and partition values. `ADAPTIVE`: The primary and subpartitions of the tables added to the table group must be consistent with the primary and subpartitions of all tables in the table group. This includes the partitioning type, number of partitions, and partition values. Consistent partitioning includes: Same partitioning type (for example, both are Hash+Range partitioned). If the partitioning type is Hash, the number of referenced columns and the number of partitions must be the same. If the partitioning type is Range, the number of referenced columns, the number of partitions, and the range split points must be the same. For subpartitions, the requirements are the same as those for primary partitions.

The property of the table group. If the SHARDING property is not specified, the default value is ADAPTIVE. Valid values of SHARDING are as follows:

NONE: No restrictions are imposed on the tables added to the table group.
PARTITION: The primary partitions of the tables added to the table group must be consistent with the primary partitions of all tables in the table group. This includes the partitioning type, number of partitions, and partition values.
ADAPTIVE: The primary and subpartitions of the tables added to the table group must be consistent with the primary and subpartitions of all tables in the table group. This includes the partitioning type, number of partitions, and partition values.

Consistent partitioning includes:

Same partitioning type (for example, both are Hash+Range partitioned).
If the partitioning type is Hash, the number of referenced columns and the number of partitions must be the same.
If the partitioning type is Range, the number of referenced columns, the number of partitions, and the range split points must be the same.

For subpartitions, the requirements are the same as those for primary partitions.

Examples

Create a table group named tblgroup1 with the SHARDING attribute set to NONE, and add the partitioned tables tbl1 and tbl2 to the 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 the partitioning methods of tbl1 and tbl2 are consistent, 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 the SHARDING attribute set to NONE, and 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 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 the SHARDING attribute set to NONE, and add the partitioned tables tbl1 and tbl2 to the 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 primary partitions of tbl1 and tbl2 in the table group tblgroup3 have the same partitioning type but different subpartitioning types (with different numbers of subpartitions), 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 the SHARDING attribute set to ADAPTIVE, and modify the number of partitions of the tables tbl1 and tbl2 in the table group. After the modification, the partitioning methods of all tables in the table group are inconsistent, and new tables such as tbl3 cannot 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