Note
This view is available starting with V2.2.77.
Purpose
This view displays the subpartition name, table name, partition to which the subpartition belongs, and storage attributes of each subpartition of the tables owned by the current user.
Applicability
This view is available only in Oracle mode of OceanBase Database.
Columns
| Column | Type | Nullable? | Description | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| TABLE_NAME | VARCHAR2(128) | NO | The name of the table. | ||||||||||
| PARTITION_NAME | VARCHAR2(128) | NO | The name of the partition. | ||||||||||
| SUBPARTITION_NAME | VARCHAR2(128) | NO | The name of the subpartition. | ||||||||||
| HIGH_VALUE | VARCHAR2(32767) | NO | The subpartition expression. | ||||||||||
| HIGH_VALUE_LENGTH | NUMBER | NO | The length of the subpartition expression. | ||||||||||
| PARTITION_POSITION | NUMBER | NO | The position of the partition. | ||||||||||
| SUBPARTITION_POSITION | NUMBER | NO | The position of the subpartition within the partition. | ||||||||||
| TABLESPACE_NAME | VARCHAR2(30) | NO | The name of the tablespace that contains the subpartition. | ||||||||||
| PCT_FREE | NUMBER | NO | This column is not supported. It is set to NULL by default. | ||||||||||
| PCT_USED | NUMBER | NO | This column is not supported. It is set to NULL by default. | ||||||||||
| INI_TRANS | NUMBER | NO | This column is not supported. It is set to NULL by default. | ||||||||||
| MAX_TRANS | NUMBER | NO | This column is not supported. It is set to NULL by default. | ||||||||||
| INITIAL_EXTENT | NUMBER | NO | This column is not supported. It is set to NULL by default. | ||||||||||
| NEXT_EXTENT | NUMBER | NO | This column is not supported. It is set to NULL by default. | ||||||||||
| MIN_EXTENT | NUMBER | NO | This column is not supported. It is set to NULL by default. | ||||||||||
| MAX_EXTENT | NUMBER | NO | This column is not supported. It is set to NULL by default. | ||||||||||
| MAX_SIZE | NUMBER | NO | This column is not supported. It is set to NULL by default. | ||||||||||
| PCT_INCREASE | NUMBER | NO | This column is not supported. It is set to NULL by default. | ||||||||||
| FREELISTS | NUMBER | NO | This column is not supported. It is set to NULL by default. | ||||||||||
| FREELIST_GROUPS | NUMBER | NO | This column is not supported. It is set to NULL by default. | ||||||||||
| LOGGING | VARCHAR2(3) | NO | This column is not supported. It is set to NULL by default. | ||||||||||
| COMPRESSION | VARCHAR2(8) | NO | Indicates whether the subpartition is compressed. | ||||||||||
| COMPRESS_FOR | VARCHAR2(30) | NO | The compression method. | ||||||||||
| NUM_ROWS | NUMBER | NO | This column is not supported. It is set to NULL by default. | ||||||||||
| BLOCKS | NUMBER | NO | This column is not supported. It is set to NULL by default. | ||||||||||
| EMPTY_BLOCKS | NUMBER | NO | This column is not supported. It is set to NULL by default. | ||||||||||
| AVG_SPACE | NUMBER | NO | This column is not supported. It is set to NULL by default. | ||||||||||
| CHAIN_CNT | NUMBER | NO | This column is not supported. It is set to NULL by default. | ||||||||||
| AVG_ROW_LEN | NUMBER | NO | This column is not supported. It is set to NULL by default. | ||||||||||
| SAMPLE_SIZE | NUMBER | NO | This column is not supported. It is set to NULL by default. | ||||||||||
| LAST_ANALYZED | DATE | NO | This column is not supported. It is set to NULL by default. | ||||||||||
| BUFFER_POOL | VARCHAR2(7) | NO | This column is not supported. It is set to NULL by default. | ||||||||||
| FLASH_CACHE | VARCHAR2(7) | NO | This column is not supported. It is set to NULL by default. | ||||||||||
| CELL_FLASH_CACHE | VARCHAR2(7) | NO | This column is not supported. It is set to NULL by default. | ||||||||||
| GLOBAL_STATS | VARCHAR2(3) | NO | This column is not supported. It is set to NULL by default. | ||||||||||
| USER_STATS | VARCHAR2(3) | NO | This column is not supported. It is set to NULL by default. | ||||||||||
| INTERVAL | VARCHAR2(3) | NO | This column is not supported. It is set to NULL by default. | ||||||||||
| SEGMENT_CREATED | VARCHAR2(3) | NO | This column is not supported. It is set to NULL by default. | ||||||||||
| INDEXING | VARCHAR2(3) | NO | This column is set to NULL by default. | ||||||||||
| READ_ONLY | VARCHAR2(3) | NO | This column is set to NULL by default. | ||||||||||
| INMEMORY | VARCHAR2(8) | NO | The Oracle-compliant field, whose value is NULL and is not meaningful in practice. | ||||||||||
| Column | Type | Nullable? | Description | ----------------- | ------------------ | ---------- | -------------------------- | INMEMORY_PRIORITY | VARCHAR2(8) | NO | A column compatible with Oracle, with a value of NULL, and no actual meaning. | ||
| INMEMORY_DISTRIBUTE | VARCHAR2(15) | NO | An Oracle-compatible column that allows null values. | ||||||||||
| INMEMORY_COMPRESSION | VARCHAR2(17) | NO | This field is a compatibility field for Oracle. The field value is NULL and does not have any practical meaning. | ||||||||||
| INMEMORY_DUPLICATE | VARCHAR2(13) | NO | This field is a compatibility field for Oracle. Its value is NULL and has no actual meaning. | ||||||||||
| INMEMORY_SERVICE | VARCHAR2(12) | NO | This field is reserved for Oracle and the value is NULL. It does not have a specific meaning. | INMEMORY_SERVICE | VARCHAR2(12) | NO | This field is reserved for Oracle and the value is NULL. It does not have a specific meaning. | ||||||
| Column | Type | Nullable | Description | --------------------- | --------------------- | ----------- | ----------------------------------------- | INMEMORY_SERVICE_NAME | VARCHAR2(1000) | NO | This column is compatible with the INMEMORY_SERVICE_NAME column in Oracle. The value is NULL, which has no actual meaning. | ||
| CELLMEMORY | VARCHAR2(24) | NO | This field is compatible with the ORACLE database. The value is NULL, which has no actual meaning. | ||||||||||
| MEMOPTIMIZE_READ | VARCHAR2(8) | NO | This column is not nullable | ||||||||||
| MEMOPTIMIZE_WRITE | VARCHAR2(8) | NO | This column is NULL by default. |
Sample query
Query the subpartition information of the partitioned table TBL2_F_RL in the current user's schema.
obclient [SYS]> SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, HIGH_VALUE, HIGH_VALUE_LENGTH, PARTITION_POSITION, SUBPARTITION_POSITION
FROM SYS.USER_TAB_SUBPARTITIONS
WHERE TABLE_NAME='TBL2_F_RL';
The query result is as follows:
+------------+----------------+-------------------+------------+-------------------+--------------------+-----------------------+
| TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | HIGH_VALUE | HIGH_VALUE_LENGTH | PARTITION_POSITION | SUBPARTITION_POSITION |
+------------+----------------+-------------------+------------+-------------------+--------------------+-----------------------+
| TBL2_F_RL | P0 | SP0 | 1,3 | 3 | 1 | 1 |
| TBL2_F_RL | P0 | SP1 | 4,6 | 3 | 1 | 2 |
| TBL2_F_RL | P0 | SP2 | 7,9 | 3 | 1 | 3 |
| TBL2_F_RL | P1 | SP3 | 1,3 | 3 | 2 | 1 |
| TBL2_F_RL | P1 | SP4 | 4,6 | 3 | 2 | 2 |
| TBL2_F_RL | P1 | SP5 | 7,9 | 3 | 2 | 3 |
+------------+----------------+-------------------+------------+-------------------+--------------------+-----------------------+
6 rows in set
References
Query the subpartition information of all partitioned tables in the current schema: DBA_TAB_SUBPARTITIONS
Query the subpartition information of all partitioned tables accessible to the current user: ALL_TAB_SUBPARTITIONS
Query the partition information of all partitioned tables owned by the current user: USER_TAB_PARTITIONS