Note
This view is available starting with V4.0.0.
Purpose
This view displays the subpartition information of the partitioned tables in the current tenant. The information includes the subpartition name, table name, partition name, storage attributes, and statistics generated by the DBMS_STATS package.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| TABLE_OWNER | varchar(128) | NO | The owner of the table. |
| TABLE_NAME | varchar(128) | NO | The name of the table. |
| PARTITION_NAME | varchar(128) | NO | The name of the partition. |
| SUBPARTITION_NAME | varchar(128) | NO | The name of the subpartition. |
| HIGH_VALUE | mediumtext | NO | The subpartition expression. |
| HIGH_VALUE_LENGTH | bigint(20) | NO | The length of the subpartition expression. |
| PARTITION_POSITION | bigint(20) | NO | The position of the partition. |
| SUBPARTITION_POSITION | bigint(20) | NO | The position of the subpartition in the partition. |
| TABLESPACE_NAME | varchar(30) | NO | The name of the tablespace that contains the subpartition. |
| PCT_FREE | bigint(0) | NO | This column is not supported. The value of this column is NULL by default. |
| PCT_USED | bigint(0) | NO | This column is not supported. The value of this column is NULL by default. |
| INI_TRANS | bigint(0) | NO | This column is not supported. The value of this column is NULL by default. |
| MAX_TRANS | bigint(0) | NO | This column is not supported. The value of this column is NULL by default. |
| INITIAL_EXTENT | bigint(0) | NO | This column is not supported. The value of this column is NULL by default. |
| NEXT_EXTENT | bigint(0) | NO | This column is not supported. The value of this column is NULL by default. |
| MIN_EXTENT | bigint(0) | NO | This column is not supported. The value of this column is NULL by default. |
| MAX_EXTENT | bigint(0) | NO | This column is not supported. The value of this column is NULL by default. |
| MAX_SIZE | bigint(0) | NO | This column is not supported. The value of this column is NULL by default. |
| PCT_INCREASE | bigint(0) | NO | This column is not supported. The value of this column is NULL by default. |
| FREELISTS | bigint(0) | NO | This column is not supported. The value of this column is NULL by default. |
| FREELIST_GROUPS | bigint(0) | NO | This column is not supported. The value of this column is NULL by default. |
| LOGGING | varchar(3) | NO | This column is not supported. The value of this column is NULL by default. |
| COMPRESSION | varchar(8) | NO | Indicates whether the subpartition is compressed. |
| COMPRESS_FOR | varchar(30) | NO | The compression method. |
| NUM_ROWS | bigint(0) | NO | This column is not supported. The value of this column is NULL by default. |
| BLOCKS | bigint(0) | NO | This column is not supported. The value of this column is NULL by default. |
| EMPTY_BLOCKS | bigint(0) | NO | This column is not supported. The value of this column is NULL by default. |
| AVG_SPACE | bigint(0) | NO | This column is not supported. The value of this column is NULL by default. |
| CHAIN_CNT | bigint(0) | NO | This column is not supported. The value of this column is NULL by default. |
| AVG_ROW_LEN | bigint(0) | NO | This column is not supported. The value of this column is NULL by default. |
| SAMPLE_SIZE | bigint(0) | NO | This column is not supported. The value of this column is NULL by default. |
| LAST_ANALYZED | date | NO | This column is not supported. The value of this column is NULL by default. |
| BUFFER_POOL | varchar(7) | NO | This column is not supported. The value of this column is NULL by default. |
| FLASH_CACHE | varchar(7) | NO | This column is not supported. The value of this column is NULL by default. |
| CELL_FLASH_CACHE | varchar(7) | NO | This column is not supported. The value of this column is NULL by default. |
| GLOBAL_STATS | varchar(3) | NO | This column is not supported. The value of this column is NULL by default. |
| USER_STATS | varchar(3) | NO | This column is not supported. The value of this column is NULL by default. |
| INTERVAL | varchar(3) | NO | This column is not supported. The value of this column is NULL by default. |
| SEGMENT_CREATED | varchar(3) | NO | This column is not supported. The value of this column is NULL by default. |
| INDEXING | varchar(3) | NO | This column is NULL by default. |
| READ_ONLY | varchar(3) | NO | The value of this column is NULL by default. |
| INMEMORY | varchar(8) | NO | The value of this column defaults to NULL. |
| INMEMORY_PRIORITY | VARCHAR(8) | NO | The value of this column is NULL by default. |
| INMEMORY_DISTRIBUTE | varchar(15) | NO | This column is currently not supported. It is set to NULL by default. |
| INMEMORY_COMPRESSION | varchar(17) | NO | NULL by default |
| INMEMORY_DUPLICATE | varchar(13) | NO | This column is NULL by default. |
| INMEMORY_SERVICE | varchar(12) | NO | This column is reserved and will be NULL by default. |
| INMEMORY_SERVICE_NAME | varchar(1000) | NO | This field defaults to NULL. |
| CELLMEMORY | varchar(24) | NO | This column is not supported currently and is set to NULL by default. |
| MEMOPTIMIZE_READ | varchar(8) | NO | This column is NULL by default. |
| MEMOPTIMIZE_WRITE | varchar(8) | NO | This column is set to NULL by default. |
Sample query
In the sys tenant, query the subpartition information of the t_m_rclc table in the current tenant.
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
Query the subpartition information of all tenant partitioned tables: CDB_TAB_SUBPARTITIONS
Query the partition information of the partitioned tables in the current tenant: DBA_TAB_PARTITIONS