Note
This view is available starting with V4.0.0.
Overview
Displays the subpartition information of partitioned tables for the current tenant, including the subpartition name, the name of the table and its partition, storage attributes, and statistics generated by the DBMS_STATS package.
Columns
Field Name |
Type |
Whether can be NULL |
Description |
|---|---|---|---|
| TABLE_OWNER | varchar(128) | NO | Owner of the table |
| TABLE_NAME | varchar(128) | NO | Table name |
| PARTITION_NAME | varchar(128) | NO | Partition Name |
| SUBPARTITION_NAME | varchar(128) | NO | Subpartition Name |
| HIGH_VALUE | mediumtext | NO | Subpartition expression |
| HIGH_VALUE_LENGTH | bigint(20) | NO | Length of Subpartition Expression |
| PARTITION_POSITION | bigint(20) | NO | Partition Location |
| SUBPARTITION_POSITION | bigint(20) | NO | The position of the subpartition within the partition. |
| TABLESPACE_NAME | varchar(30) | NO | Name of the tablespace containing the subpartition |
| PCT_FREE | bigint(0) | NO | This field is not supported. Currently, the default value of this field is NULL. |
| PCT_USED | bigint(0) | NO | This field is not supported. Currently, the default value of this field is NULL. |
| INI_TRANS | bigint(0) | NO | This field is not supported. Currently, the default value of this field is NULL. |
| MAX_TRANS | bigint(0) | NO | This field is not supported. Currently, the default value of this field is NULL. |
| INITIAL_EXTENT | bigint(0) | NO | This field is not supported. Currently, the default value of this field is NULL. |
| NEXT_EXTENT | bigint(0) | NO | This field is not supported. Currently, the default value of this field is NULL. |
| MIN_EXTENT | bigint(0) | NO | This field is not supported. Currently, the default value of this field is NULL. |
| MAX_EXTENT | bigint(0) | NO | This field is not supported. Currently, the default value of this field is NULL. |
| MAX_SIZE | bigint(0) | NO | This field is not supported. Currently, the default value of this field is NULL. |
| PCT_INCREASE | bigint(0) | NO | This field is not supported. Currently, the default value of this field is NULL. |
| FREELISTS | bigint(0) | NO | This field is not supported. Currently, the default value of this field is NULL. |
| FREELIST_GROUPS | bigint(0) | NO | This field is not supported. Currently, the default value of this field is NULL. |
| LOGGING | varchar(3) | NO | This field is not supported. Currently, the default value of this field is NULL. |
| COMPRESSION | varchar(8) | NO | Whether Subpartition is Compressed |
| COMPRESS_FOR | varchar(30) | NO | Compression Method |
| NUM_ROWS | bigint(0) | NO | This field is not supported. Currently, the default value of this field is NULL. |
| BLOCKS | bigint(0) | NO | This field is not supported. Currently, the default value of this field is NULL. |
| EMPTY_BLOCKS | bigint(0) | NO | This field is not supported. Currently, the default value of this field is NULL. |
| AVG_SPACE | bigint(0) | NO | This field is not supported. Currently, the default value of this field is NULL. |
| CHAIN_CNT | bigint(0) | NO | This field is not supported. Currently, the default value of this field is NULL. |
| AVG_ROW_LEN | bigint(0) | NO | This field is not supported. Currently, the default value of this field is NULL. |
| SAMPLE_SIZE | bigint(0) | NO | This field is not supported. Currently, the default value of this field is NULL. |
| LAST_ANALYZED | date | NO | This field is not supported. Currently, the default value of this field is NULL. |
| BUFFER_POOL | varchar(7) | NO | This field is not supported. Currently, the default value of this field is NULL. |
| FLASH_CACHE | varchar(7) | NO | This field is not supported. Currently, the default value of this field is NULL. |
| CELL_FLASH_CACHE | varchar(7) | NO | This field is not supported. Currently, the default value of this field is NULL. |
| GLOBAL_STATS | varchar(3) | NO | This field is not supported. Currently, the default value of this field is NULL. |
| USER_STATS | varchar(3) | NO | This field is not supported. Currently, the default value of this field is NULL. |
| INTERVAL | varchar(3) | NO | This field is not supported. Currently, the default value of this field is NULL. |
| SEGMENT_CREATED | varchar(3) | NO | This field is not supported. Currently, the default value of this field is NULL. |
| INDEXING | varchar(3) | NO | The default value of this field is currently NULL. |
| READ_ONLY | varchar(3) | NO | The default value of this field is currently NULL. |
| INMEMORY | varchar(8) | NO | The default value of this field is currently NULL. |
| INMEMORY_PRIORITY | varchar(8) | NO | The default value of this field is currently NULL. |
| INMEMORY_DISTRIBUTE | varchar(15) | NO | This field is not supported. Currently, the default value of this field is NULL. |
| INMEMORY_COMPRESSION | varchar(17) | NO | The default value of this field is currently NULL. |
| INMEMORY_DUPLICATE | varchar(13) | NO | The default value of this field is currently NULL. |
| INMEMORY_SERVICE | varchar(12) | NO | The default value of this field is currently NULL. |
| INMEMORY_SERVICE_NAME | varchar(1000) | NO | The default value of this field is currently NULL. |
| CELLMEMORY | varchar(24) | NO | This field is not supported. Currently, the default value of this field is NULL. |
| MEMOPTIMIZE_READ | varchar(8) | NO | The default value of this field is currently NULL. |
| MEMOPTIMIZE_WRITE | varchar(8) | NO | The default value of this field is currently NULL. |
Sample query
The system tenant queries the subpartition information of its own partitioned table t_m_rclc.
obclient [oceanbase]> SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, HIGH_VALUE, HIGH_VALUE_LENGTH, PARTITION_POSITION, SUBPARTITION_POSITION FROM oceanbase.DBA_TAB_SUBPARTITIONS WHERE TABLE_NAME='t_m_rclc';
The query result is as follows:
+-------------+------------+----------------+-------------------+------------+-------------------+--------------------+-----------------------+
| TABLE_OWNER | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | HIGH_VALUE | HIGH_VALUE_LENGTH | PARTITION_POSITION | SUBPARTITION_POSITION |
+-------------+------------+----------------+-------------------+------------+-------------------+--------------------+-----------------------+
| test | t_m_rclc | p0 | p0smp0 | 1,3 | 3 | 1 | 1 |
| test | t_m_rclc | p0 | p0smp1 | 4,6 | 3 | 1 | 2 |
| test | t_m_rclc | p0 | p0smp2 | 7 | 1 | 1 | 3 |
| test | t_m_rclc | p1 | p1smp0 | 1,3 | 3 | 2 | 1 |
| test | t_m_rclc | p1 | p1smp1 | 4,6 | 3 | 2 | 2 |
| test | t_m_rclc | p1 | p1smp2 | 7 | 1 | 2 | 3 |
| test | t_m_rclc | p2 | p2smp0 | 1,3 | 3 | 3 | 1 |
| test | t_m_rclc | p2 | p2smp1 | 4,6 | 3 | 3 | 2 |
| test | t_m_rclc | p2 | p2smp2 | 7 | 1 | 3 | 3 |
+-------------+------------+----------------+-------------------+------------+-------------------+--------------------+-----------------------+
9 rows in set
References
View the subpartition information of partitioned tables for all tenants: CDB_TAB_SUBPARTITIONS
View detailed information about partitions of a partitioned table in the current tenant: DBA_TAB_PARTITIONS