Note
This view is available starting with V4.0.0.
Purpose
This view displays the subpartition information of all tenants' partitioned tables, including the subpartition name, table name, partition name, storage attributes, and statistics generated by the DBMS_STATS package.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| CON_ID | bigint(20) | NO | The tenant ID. |
| 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 | varchar(262144) | 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 within 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 default value is NULL. |
| PCT_USED | bigint(0) | NO | This column is not supported. The default value is NULL. |
| INI_TRANS | bigint(0) | NO | This column is not supported. The default value is NULL. |
| MAX_TRANS | bigint(0) | NO | This column is not supported. The default value is NULL. |
| INITIAL_EXTENT | bigint(0) | NO | This column is not supported. The default value is NULL. |
| NEXT_EXTENT | bigint(0) | NO | This column is not supported. The default value is NULL. |
| MIN_EXTENT | bigint(0) | NO | This column is not supported. The default value is NULL. |
| MAX_EXTENT | bigint(0) | NO | This column is not supported. The default value is NULL. |
| MAX_SIZE | bigint(0) | NO | This column is not supported. The default value is NULL. |
| PCT_INCREASE | bigint(0) | NO | This column is not supported. The default value is NULL. |
| FREELISTS | bigint(0) | NO | This column is not supported. The default value is NULL. |
| FREELIST_GROUPS | bigint(0) | NO | This column is not supported. The default value is NULL. |
| LOGGING | varchar(3) | NO | This column is not supported. The default value is NULL. |
| 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 default value is NULL. |
| BLOCKS | bigint(0) | NO | This column is not supported. The default value is NULL. |
| EMPTY_BLOCKS | bigint(0) | NO | This column is not supported. The default value is NULL. |
| AVG_SPACE | bigint(0) | NO | This column is not supported. The default value is NULL. |
| CHAIN_CNT | bigint(0) | NO | This column is not supported. The default value is NULL. |
| AVG_ROW_LEN | bigint(0) | NO | This column is not supported. The default value is NULL. |
| SAMPLE_SIZE | bigint(0) | NO | This column is not supported. The default value is NULL. |
| LAST_ANALYZED | date | NO | This column is not supported. The default value is NULL. |
| BUFFER_POOL | varchar(7) | NO | This column is not supported. The default value is NULL. |
| FLASH_CACHE | varchar(7) | NO | This column is not supported. The default value is NULL. |
| CELL_FLASH_CACHE | varchar(7) | NO | This column is not supported. The default value is NULL. |
| GLOBAL_STATS | varchar(3) | NO | This column is not supported. The default value is NULL. |
| USER_STATS | varchar(3) | NO | This column is not supported. The default value is NULL. |
| INTERVAL | varchar(3) | NO | This column is not supported. The default value is NO. |
| SEGMENT_CREATED | varchar(3) | NO | This column is not supported. The default value is NULL. |
| INDEXING | varchar(3) | NO | This column is NULL by default. |
| READ_ONLY | varchar(3) | NO | This column is NULL by default. |
| INMEMORY | varchar(8) | NO | This column is NULL by default. |
| INMEMORY_PRIORITY | varchar(8) | NO | This column is NULL by default. |
| INMEMORY_DISTRIBUTE | varchar(15) | NO | This column is NULL by default. |
| INMEMORY_COMPRESSION | varchar(17) | NO | This column is NULL by default. |
| INMEMORY_DUPLICATE | varchar(13) | NO | This column is NULL by default. |
| INMEMORY_SERVICE | varchar(12) | NO | This column is NULL by default. |
| INMEMORY_SERVICE_NAME | varchar(1000) | NO | This column is NULL by default. |
| CELLMEMORY | varchar(24) | NO | This column is NULL by default. |
| MEMOPTIMIZE_READ | varchar(8) | NO | This column is NULL by default. |
| MEMOPTIMIZE_WRITE | varchar(8) | NO | This column is NULL by default. |
Sample query
In the sys tenant, query the subpartition information of the partitioned table tbl2_f_rl in the tenant with ID 1002.
obclient [oceanbase]> SELECT CON_ID, TABLE_OWNER, TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, HIGH_VALUE, HIGH_VALUE_LENGTH, PARTITION_POSITION, SUBPARTITION_POSITION FROM oceanbase.CDB_TAB_SUBPARTITIONS WHERE CON_ID=1002 AND TABLE_NAME='tbl2_f_rl';
The query result is as follows:
+--------+-------------+------------+----------------+-------------------+------------+-------------------+--------------------+-----------------------+
| CON_ID | TABLE_OWNER | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | HIGH_VALUE | HIGH_VALUE_LENGTH | PARTITION_POSITION | SUBPARTITION_POSITION |
+--------+-------------+------------+----------------+-------------------+------------+-------------------+--------------------+-----------------------+
| 1002 | infotest | tbl2_f_rl | p0 | sp0 | 1,3 | 3 | 1 | 1 |
| 1002 | infotest | tbl2_f_rl | p0 | sp1 | 4,6 | 3 | 1 | 2 |
| 1002 | infotest | tbl2_f_rl | p0 | sp2 | 7,9 | 3 | 1 | 3 |
| 1002 | infotest | tbl2_f_rl | p1 | sp3 | 1,3 | 3 | 2 | 1 |
| 1002 | infotest | tbl2_f_rl | p1 | sp4 | 4,6 | 3 | 2 | 2 |
| 1002 | infotest | tbl2_f_rl | p1 | sp5 | 7,9 | 3 | 2 | 3 |
+--------+-------------+------------+----------------+-------------------+------------+-------------------+--------------------+-----------------------+
6 rows in set
References
View the subpartition information of the partitioned tables in the current tenant: DBA_TAB_SUBPARTITIONS
View the partition details of all partitioned tables in all tenants: CDB_TAB_PARTITIONS
