Overview
Displays the subpartition information for all tenant partitioned tables, including the subpartition name, the name of the table and its partition, their 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 currently supported. The default value of this field is NO |
| 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 NULL. |
| READ_ONLY | varchar(3) | NO | The default value of this field is NULL. |
| INMEMORY | varchar(8) | NO | The default value of this field is NULL. |
| INMEMORY_PRIORITY | varchar(8) | NO | The default value of this field is 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 NULL. |
| INMEMORY_DUPLICATE | varchar(13) | NO | The default value of this field is NULL. |
| INMEMORY_SERVICE | varchar(12) | NO | The default value of this field is NULL. |
| INMEMORY_SERVICE_NAME | varchar(1000) | NO | The default value of this field is 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 NULL. |
| MEMOPTIMIZE_WRITE | varchar(8) | NO | The default value of this field is NULL. |
| STORAGE_CACHE_POLICY | varchar(256) | NO | Represents the data caching strategy. |
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 subpartition information of partitioned tables in the current tenant: DBA_TAB_SUBPARTITIONS
View the details of partitions in a partitioned table for all tenants: CDB_TAB_PARTITIONS