Note
This view is available starting with V4.0.0.
Overview
Displays the subpartition information for all tenant partitioned tables, including the subpartition name, table and partition name, storage attributes, and statistics generated by the DBMS_STATS package.
Columns
Field Name |
Type |
Whether can be NULL |
Description |
|---|---|---|---|
| CON_ID | bigint(20) | NO | Tenant ID |
| 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 | varchar(262144) | 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 the partitioned table tbl2_f_rl for 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 partitioned tables in the current tenant: DBA_TAB_SUBPARTITIONS
View detailed information about partitions of partitioned tables for all tenants: CDB_TAB_PARTITIONS