Note
This view is available starting with V2.2.77.
Purpose
This view displays the subpartition information of partitioned tables in the current tenant, including the subpartition name, table name, partition name, storage attributes, and statistics generated by the DBMS_STATS package.
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 default value is NULL. |
| PCT_USED | NUMBER | NO | This column is not supported. The default value is NULL. |
| INI_TRANS | NUMBER | NO | This column is not supported. The default value is NULL. |
| MAX_TRANS | NUMBER | NO | This column is not supported. The default value is NULL. |
| INITIAL_EXTENT | NUMBER | NO | This column is not supported. The default value is NULL. |
| NEXT_EXTENT | NUMBER | NO | This column is not supported. The default value is NULL. |
| MIN_EXTENT | NUMBER | NO | This column is not supported. The default value is NULL. |
| MAX_EXTENT | NUMBER | NO | This column is not supported. The default value is NULL. |
| MAX_SIZE | NUMBER | NO | This column is not supported. The default value is NULL. |
| PCT_INCREASE | NUMBER | NO | This column is not supported. The default value is NULL. |
| FREELISTS | NUMBER | NO | This column is not supported. The default value is NULL. |
| FREELIST_GROUPS | NUMBER | NO | This column is not supported. The default value is NULL. |
| LOGGING | VARCHAR2(3) | NO | This column is not supported. The default value is NULL. |
| 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 default value is NULL. |
| BLOCKS | NUMBER | NO | This column is not supported. The default value is NULL. |
| EMPTY_BLOCKS | NUMBER | NO | This column is not supported. The default value is NULL. |
| AVG_SPACE | NUMBER | NO | This column is not supported. The default value is NULL. |
| CHAIN_CNT | NUMBER | NO | This column is not supported. The default value is NULL. |
| AVG_ROW_LEN | NUMBER | NO | This column is not supported. The default value is NULL. |
| SAMPLE_SIZE | NUMBER | 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 | VARCHAR2(7) | NO | This column is not supported. The default value is NULL. |
| FLASH_CACHE | VARCHAR2(7) | NO | This column is not supported. The default value is NULL. |
| CELL_FLASH_CACHE | VARCHAR2(7) | NO | This column is not supported. The default value is NULL. |
| GLOBAL_STATS | VARCHAR2(3) | NO | This column is not supported. The default value is NULL. |
| USER_STATS | VARCHAR2(3) | NO | This column is not supported. The default value is NULL. |
| INTERVAL | VARCHAR2(3) | NO | This column is not supported. The default value is NO. |
| SEGMENT_CREATED | VARCHAR2(3) | NO | This column is not supported. The default value is NULL. |
| INDEXING | VARCHAR2(3) | NO | The default value is NULL. |
| READ_ONLY | VARCHAR2(3) | NO | This column is NULL by default. |
| INMEMORY | VARCHAR2(8) | NO | This column is compatible with the Oracle INMEMORY column. It is set to NULL and has no meaningful value. |
| INMEMORY_PRIORITY | VARCHAR2(8) | NO | This column is compatible with the Oracle INMEMORY_PRIORITY column. It is set to NULL and has no meaningful value. |
| INMEMORY_DISTRIBUTE | VARCHAR2(15) | NO | This column is compatible with the Oracle INMEMORY_DISTRIBUTE column. It is set to NULL and has no meaningful value. |
| INMEMORY_COMPRESSION | VARCHAR2(17) | NO | This column is compatible with the Oracle INMEMORY_COMPRESSION column. It is set to NULL and has no meaningful value. |
| INMEMORY_DUPLICATE | VARCHAR2(13) | NO | This column is compatible with the Oracle INMEMORY_DUPLICATE column. It is set to NULL and has no meaningful value. |
| INMEMORY_SERVICE | VARCHAR2(12) | NO | This column is compatible with the Oracle INMEMORY_SERVICE column. It is set to NULL and has no meaningful value. |
| INMEMORY_SERVICE_NAME | VARCHAR2(1000) | NO | This column is compatible with the Oracle INMEMORY_SERVICE_NAME column. It is set to NULL and has no meaningful value. |
| CELLMEMORY | VARCHAR2(24) | NO | This column is compatible with the Oracle CELLMEMORY column. It is set to NULL and has no meaningful value. |
| MEMOPTIMIZE_READ | VARCHAR2(8) | NO | This column is NULL by default. |
| MEMOPTIMIZE_WRITE | VARCHAR2(8) | NO | This column is NULL by default. |
Sample query
Query the TBL2_F_RL table in the current user's tenant to view the subpartition information.
obclient [SYS]> SELECT TABLE_OWNER,TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, HIGH_VALUE, HIGH_VALUE_LENGTH, PARTITION_POSITION, SUBPARTITION_POSITION
FROM SYS.DBA_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
View the subpartition information of all tenant partitioned tables: CDB_TAB_SUBPARTITIONS
View the subpartition information of partitioned tables accessible to the current user: ALL_TAB_SUBPARTITIONS
View the subpartition information of partitioned tables owned by the current user: USER_TAB_SUBPARTITIONS.
View the partition details of partitioned tables in the current user's tenant: DBA_TAB_PARTITIONS