After you create a table group, you can view its information, including the SHARDING attribute, tables in the table group, time when the table group was created, and location information of tables in the table group.
View the SHARDING attribute of a table group
Query views for the attributes of a table group and the partition information about tables in the table group
You can query the DBA_OB_TABLEGROUPS view in an Oracle user tenant for the SHARDING attribute of a table group and the partition information about tables in the table group. A sample statement is as follows:
SELECT * FROM SYS.DBA_OB_TABLEGROUPS WHERE tablegroup_name = 'TBLGROUP1';
The query result is as follows:
+-----------------+-------------------+----------------------+-----------------+------------------------+------------------------+---------------------------+----------+
| TABLEGROUP_NAME | PARTITIONING_TYPE | SUBPARTITIONING_TYPE | PARTITION_COUNT | DEF_SUBPARTITION_COUNT | PARTITIONING_KEY_COUNT | SUBPARTITIONING_KEY_COUNT | SHARDING |
+-----------------+-------------------+----------------------+-----------------+------------------------+------------------------+---------------------------+----------+
| TBLGROUP1 | NONE | NONE | NULL | NULL | NULL | NULL | NONE |
+-----------------+-------------------+----------------------+-----------------+------------------------+------------------------+---------------------------+----------+
1 row in set
Fields in the query result are described as follows:
TABLEGROUP_NAME: the name of the table group.PARTITIONING_TYPE,SUBPARTITIONING_TYPE,PARTITION_COUNT,DEF_SUBPARTITION_COUNT,PARTITIONING_KEY_COUNT, andSUBPARTITIONING_KEY_COUNT: the attributes of the table group with partition definitions in OceanBase Database V3.x. In OceanBase Database V4.3.3, the value of these attributes isNONEorNULL.SHARDING: theSHARDINGattribute of the table group. Valid values of theSHARDINGattribute are as follows:For a table group with the
SHARDINGattribute set toNONE, all partitions of all tables in the table group are aggregated on the same server, and no limitations are imposed on tables to be added to the table group.For a table group with the
SHARDINGattribute set toPARTITION, the data of each table in the table group is scattered by partition. For a subpartitioned table in the table group, all subpartitions in each partition are aggregated together.Partition requirement: A table to be added to the table group must have the same partition definition as existing tables in the table group, including the partitioning type, partition count, and partition value.
For a table group with the
SHARDINGattribute set toADAPTIVE, the data of each table in the table group is adaptively scattered. In other words, data in partitioned tables is scattered by partition, and data in subpartitioned tables is scattered by subpartition in each partition.Partition requirement: A table to be added to the table group must have the same partition and subpartition definitions as existing tables in the table group, including the partitioning type, partition count, and partition value.
Use the SHOW TABLEGROUPS statement to view the attributes of a table group and the databases of tables in the table group
You can also execute the SHOW TABLEGROUPS statement in an Oracle user tenant to view the SHARDING attribute of a table group and the databases to which tables in the table group belong. A sample statement is as follows:
SHOW TABLEGROUPS WHERE tablegroup_name = 'TBLGROUP1';
The query result is as follows:
+-----------------+------------+---------------+----------+
| TABLEGROUP_NAME | TABLE_NAME | DATABASE_NAME | SHARDING |
+-----------------+------------+---------------+----------+
| TBLGROUP1 | TBL1 | SYS | ADAPTIVE |
| TBLGROUP1 | TBL2 | SYS | ADAPTIVE |
+-----------------+------------+---------------+----------+
2 rows in set
Fields in the query result are described as follows:
TABLEGROUP_NAME: the name of the table group.TABLE_NAME: the name of the table.DATABASE_NAME: the name of the database to which the table belongs.SHARDING: theSHARDINGattribute of the table group. Valid values of theSHARDINGattribute are as follows:For a table group with the
SHARDINGattribute set toNONE, all partitions of all tables in the table group are aggregated on the same server, and no limitations are imposed on tables to be added to the table group.For a table group with the
SHARDINGattribute set toPARTITION, the data of each table in the table group is scattered by partition. For a subpartitioned table in the table group, all subpartitions in each partition are aggregated together.Partition requirement: A table to be added to the table group must have the same partition definition as existing tables in the table group, including the partitioning type, partition count, and partition value.
For a table group with the
SHARDINGattribute set toADAPTIVE, the data of each table in the table group is adaptively scattered. In other words, data in partitioned tables is scattered by partition, and data in subpartitioned tables is scattered by subpartition in each partition.Partition requirement: A table to be added to the table group must have the same partition and subpartition definitions as existing tables in the table group, including the partitioning type, partition count, and partition value.
View tables in a table group
You can query the DBA_OB_TABLEGROUP_TABLES view in an Oracle user tenant for the tables in a table group and the databases to which the tables belong. A sample statement is as follows:
SELECT * FROM SYS.DBA_OB_TABLEGROUP_TABLES WHERE tablegroup_name = 'TBLGROUP1';
The query result is as follows:
+-----------------+-------+------------+----------+
| TABLEGROUP_NAME | OWNER | TABLE_NAME | SHARDING |
+-----------------+-------+------------+----------+
| TBLGROUP1 | SYS | TBL1 | NONE |
| TBLGROUP1 | SYS | TBL2 | NONE |
+-----------------+-------+------------+----------+
2 rows in set
Fields in the query result are described as follows:
TABLEGROUP_NAME: the name of the table group.OWNER: the name of the database to which the table belongs.TABLE_NAME: the name of the table.SHARDING: theSHARDINGattribute of the table group. Valid values of theSHARDINGattribute are as follows:For a table group with the
SHARDINGattribute set toNONE, all partitions of all tables in the table group are aggregated on the same server, and no limitations are imposed on tables to be added to the table group.For a table group with the
SHARDINGattribute set toPARTITION, the data of each table in the table group is scattered by partition. For a subpartitioned table in the table group, all subpartitions in each partition are aggregated together.Partition requirement: A table to be added to the table group must have the same partition definition as existing tables in the table group, including the partitioning type, partition count, and partition value.
For a table group with the
SHARDINGattribute set toADAPTIVE, the data of each table in the table group is adaptively scattered. In other words, data in partitioned tables is scattered by partition, and data in subpartitioned tables is scattered by subpartition in each partition.Partition requirement: A table to be added to the table group must have the same partition and subpartition definitions as existing tables in the table group, including the partitioning type, partition count, and partition value.
View the creation time of a table group
You can query the DBA_OBJECTS view in an Oracle user tenant for information such as the creation time and last DDL operation of a table group. A sample statement is as follows:
SELECT OBJECT_NAME,CREATED, LAST_DDL_TIME FROM SYS.DBA_OBJECTS WHERE object_name = 'TBLGROUP1';
The query result is as follows:
+-------------+-----------+---------------+
| OBJECT_NAME | CREATED | LAST_DDL_TIME |
+-------------+-----------+---------------+
| TBLGROUP1 | 28-JUN-23 | 28-JUN-23 |
+-------------+-----------+---------------+
1 row in set
Fields in the query result are described as follows:
OBJECT_NAME: the object name, which is the name of the queried table group.CREATED: the time when the table group was created.LAST_DDL_TIME: the time when the last DDL operation was performed.
View the location information about tables in a table group
You can query the DBA_OB_TABLE_LOCATIONS view in an Oracle user tenant for the location information about tables in a table group, including the tablet ID of each partition, mappings between tablet IDs and log stream IDs, and location information of log stream replicas. A sample statement is as follows:
SELECT * FROM SYS.DBA_OB_TABLE_LOCATIONS WHERE tablegroup_name = 'TBLGROUP1';
The query result is as follows:
+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+----------------+----------+----------+--------------+-----------------+-----------+-----------------+---------------+----------+
| DATABASE_NAME | TABLE_NAME | TABLE_ID | TABLE_TYPE | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | DATA_TABLE_ID | TABLET_ID | LS_ID | ZONE | SVR_IP | SVR_PORT | ROLE | REPLICA_TYPE | DUPLICATE_SCOPE | OBJECT_ID | TABLEGROUP_NAME | TABLEGROUP_ID | SHARDING |
+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+----------------+----------+----------+--------------+-----------------+-----------+-----------------+---------------+----------+
| SYS | TBL1 | 500190 | USER TABLE | NULL | NULL | NULL | NULL | 200121 | 1001 | zone1 | XX.XX.XX.197 | 2882 | LEADER | FULL | NONE | 500190 | TBLGROUP1 | 500189 | NONE |
| SYS | TBL1 | 500190 | USER TABLE | NULL | NULL | NULL | NULL | 200121 | 1001 | zone2 | XX.XX.XX.194 | 2882 | FOLLOWER | FULL | NONE | 500190 | TBLGROUP1 | 500189 | NONE |
| SYS | TBL2 | 500191 | USER TABLE | P0 | NULL | NULL | NULL | 200122 | 1001 | zone1 | XX.XX.XX.197 | 2882 | LEADER | FULL | NONE | 500192 | TBLGROUP1 | 500189 | NONE |
| SYS | TBL2 | 500191 | USER TABLE | P0 | NULL | NULL | NULL | 200122 | 1001 | zone2 | XX.XX.XX.194 | 2882 | FOLLOWER | FULL | NONE | 500192 | TBLGROUP1 | 500189 | NONE |
| SYS | TBL2 | 500191 | USER TABLE | P1 | NULL | NULL | NULL | 200123 | 1001 | zone1 | XX.XX.XX.197 | 2882 | LEADER | FULL | NONE | 500193 | TBLGROUP1 | 500189 | NONE |
| SYS | TBL2 | 500191 | USER TABLE | P1 | NULL | NULL | NULL | 200123 | 1001 | zone2 | XX.XX.XX.194 | 2882 | FOLLOWER | FULL | NONE | 500193 | TBLGROUP1 | 500189 | NONE |
+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+----------------+----------+----------+--------------+-----------------+-----------+-----------------+---------------+----------+
6 rows in set
Fields in the query result are described as follows:
DATABASE_NAME: the name of the database to which the table belongs.TABLE_NAME: the name of the table.TABLE_ID: the ID of the table.TABLE_TYPE: the type of the table. Valid values:USER TABLE: a user table.SYSTEM TABLE: a system table, which is created by the system.INDEX: an index table.LOB AUX TABLE: a LOB auxiliary table, which is created by the system to implement LOB columns.
PARTITION_NAME: the name of the partition. The default value isNULLfor non-partitioned tables.SUBPARTITION_NAME: the name of the subpartition. The valueNULLindicates that no subpartition exists.INDEX_NAME: the name of the index table. For tables other than index tables, the value isNULL.DATA_TABLE_ID: the ID of the primary table. If the table is not an index table or LOB table, the value isNULL.TABLET_ID: the tablet ID of the partition.LS_ID: the ID of the log stream to which the table belongs.ZONE: the zone where the replica is located.SVR_IP: the IP address of the OBServer node where the replica is located.SVR_PORT: the port of the OBServer node where the replica is located.ROLE: the role of the log stream replica.REPLICA_TYPE: the type of the log stream replica. Valid values:FULL: a full-featured replica.READONLY: a read-only replica.COLUMNSTORE: a columnstore replica.
OBJECT_ID: the object ID. If the table is a non-partitioned table, the value is the ID of the table. If the table is a partitioned table, this field shows the ID of each partition in the table.TABLEGROUP_NAME: the name of the queried table group.TABLEGROUP_ID: the ID of the queried table group.SHARDING: theSHARDINGattribute of the table group. Valid values of theSHARDINGattribute are as follows:For a table group with the
SHARDINGattribute set toNONE, all partitions of all tables in the table group are aggregated on the same server, and no limitations are imposed on tables to be added to the table group.For a table group with the
SHARDINGattribute set toPARTITION, the data of each table in the table group is scattered by partition. For a subpartitioned table in the table group, all subpartitions in each partition are aggregated together.Partition requirement: A table to be added to the table group must have the same partition definition as existing tables in the table group, including the partitioning type, partition count, and partition value.
For a table group with the
SHARDINGattribute set toADAPTIVE, the data of each table in the table group is adaptively scattered. In other words, data in partitioned tables is scattered by partition, and data in subpartitioned tables is scattered by subpartition in each partition.Partition requirement: A table to be added to the table group must have the same partition and subpartition definitions as existing tables in the table group, including the partitioning type, partition count, and partition value.