Note
This view is available starting with V2.2.77.
Purpose
This view displays the subpartition name, table name, partition, and storage attributes of all subpartitions of tables that the current user has access to.
Applicability
This view is available only in Oracle mode.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| TABLE_OWNER | VARCHAR2(128) | NO | The owner of the table. |
| 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 in 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. The value of this column is NULL by default. |
| PCT_USED | NUMBER | NO | This column is not supported. The value of this column is NULL by default. |
| INI_TRANS | NUMBER | NO | This column is not supported. The value of this column is NULL by default. |
| MAX_TRANS | NUMBER | NO | This column is not supported. The value of this column is NULL by default. |
| INITIAL_EXTENT | NUMBER | NO | This column is not supported. The value of this column is NULL by default. |
| NEXT_EXTENT | NUMBER | NO | This column is not supported. The value of this column is NULL by default. |
| MIN_EXTENT | NUMBER | NO | This column is not supported. The value of this column is NULL by default. |
| MAX_EXTENT | NUMBER | NO | This column is not supported. The value of this column is NULL by default. |
| MAX_SIZE | NUMBER | NO | This column is not supported. The value of this column is NULL by default. |
| PCT_INCREASE | NUMBER | NO | This column is not supported. The value of this column is NULL by default. |
| FREELISTS | NUMBER | NO | This column is not supported. The value of this column is NULL by default. |
| FREELIST_GROUPS | NUMBER | NO | This column is not supported. The value of this column is NULL by default. |
| LOGGING | VARCHAR2(3) | NO | This column is not supported. The value of this column is 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. The value of this column is NULL by default. |
| BLOCKS | NUMBER | NO | This column is not supported. The value of this column is NULL by default. |
| EMPTY_BLOCKS | NUMBER | NO | This column is not supported. The value of this column is NULL by default. |
| AVG_SPACE | NUMBER | NO | This column is not supported. The value of this column is NULL by default. |
| CHAIN_CNT | NUMBER | NO | This column is not supported. The value of this column is NULL by default. |
| AVG_ROW_LEN | NUMBER | NO | This column is not supported. The value of this column is NULL by default. |
| SAMPLE_SIZE | NUMBER | 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 | VARCHAR2(7) | NO | This column is not supported. The value of this column is NULL by default. |
| FLASH_CACHE | VARCHAR2(7) | NO | This column is not supported. The value of this column is NULL by default. |
| CELL_FLASH_CACHE | VARCHAR2(7) | NO | This column is not supported. The value of this column is NULL by default. |
| GLOBAL_STATS | VARCHAR2(3) | NO | This column is not supported. The value of this column is NULL by default. |
| USER_STATS | VARCHAR2(3) | NO | This column is not supported. The value of this column is NULL by default. |
| INTERVAL | VARCHAR2(3) | NO | This column is not supported. The value of this column is NO by default. |
| SEGMENT_CREATED | VARCHAR2(3) | NO | This column is not supported. The value of this column is NULL by default. |
| INDEXING | VARCHAR2(3) | NO | This field is NULL by default. |
| READ_ONLY | VARCHAR2(3) | NO | This field is NULL by default. |
| INMEMORY | VARCHAR2(8) | NO | This field is compatible with Oracle. The value is NULL and has no practical meaning. |
| INMEMORY_PRIORITY | VARCHAR2(8) | NO | This field is compatible with Oracle. The value is NULL and has no practical meaning. |
| INMEMORY_DISTRIBUTE | VARCHAR2(15) | NO | This field is compatible with Oracle. The value is NULL and has no practical meaning. |
| INMEMORY_COMPRESSION | VARCHAR2(17) | NO | This field is compatible with Oracle. The value is NULL and has no practical meaning. |
| INMEMORY_DUPLICATE | VARCHAR2(13) | NO | This field is compatible with Oracle. The value is NULL and has no practical meaning. |
| INMEMORY_SERVICE | VARCHAR2(12) | NO | This field is compatible with Oracle. The value is NULL and has no practical meaning. |
| INMEMORY_SERVICE_NAME | VARCHAR2(1000) | NO | This field is compatible with Oracle. The value is NULL and has no practical meaning. |
| CELLMEMORY | VARCHAR2(24) | NO | This field is compatible with Oracle. The value is NULL and has no practical meaning. |
| MEMOPTIMIZE_READ | VARCHAR2(8) | NO | This field is NULL by default. |
| MEMOPTIMIZE_WRITE | VARCHAR2(8) | NO | This field is NULL by default. |
Sample query
Query the subpartition information of the TBL2_F_RL table in the partitioned tables accessible to the current user.
obclient [SYS]> SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, HIGH_VALUE, HIGH_VALUE_LENGTH, PARTITION_POSITION, SUBPARTITION_POSITION
FROM SYS.ALL_TAB_SUBPARTITIONS
WHERE TABLE_NAME='TBL2_F_RL';
The query result is as follows:
+-------------+------------+----------------+-------------------+------------+-------------------+--------------------+-----------------------+
| TABLE_OWNER | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | HIGH_VALUE | HIGH_VALUE_LENGTH | PARTITION_POSITION | SUBPARTITION_POSITION |
+-------------+------------+----------------+-------------------+------------+-------------------+--------------------+-----------------------+
| SYS | TBL2_F_RL | P0 | SP0 | 1,3 | 3 | 1 | 1 |
| SYS | TBL2_F_RL | P0 | SP1 | 4,6 | 3 | 1 | 2 |
| SYS | TBL2_F_RL | P0 | SP2 | 7,9 | 3 | 1 | 3 |
| SYS | TBL2_F_RL | P1 | SP3 | 1,3 | 3 | 2 | 1 |
| SYS | TBL2_F_RL | P1 | SP4 | 4,6 | 3 | 2 | 2 |
| SYS | 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 tenant: DBA_TAB_SUBPARTITIONS
Query the subpartition information of partitioned tables owned by the current user: USER_TAB_SUBPARTITIONS
Query the partition information of partitioned tables accessible to the current user: ALL_TAB_PARTITIONS