Note
This view is available starting with V2.2.77.
Purpose
This view displays the information about the partitions of the partitioned tables that the current user owns.
Applicability
This view is available only in Oracle mode.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| TABLE_NAME | VARCHAR2(128) | NO | The name of the partitioned table. |
| COMPOSITE | VARCHAR2(3) | NO | Indicates whether it is a composite partition. |
| PARTITION_NAME | VARCHAR2(128) | NO | The name of the partition. |
| SUBPARTITION_COUNT | NUMBER | YES | The number of subpartitions. |
| HIGH_VALUE | VARCHAR2(32767) | NO | The partitioning expression. |
| HIGH_VALUE_LENGTH | NUMBER | NO | The length of the partitioning expression. |
| PARTITION_POSITION | NUMBER | NO | The position of the partition. |
| TABLESPACE_NAME | VARCHAR2(30) | NO | The name of the tablespace to which the partition belongs. |
| PCT_FREE | NUMBER | NO | This column is not supported and is NULL by default. |
| PCT_USED | NUMBER | NO | This column is not supported and is NULL by default. |
| INI_TRANS | NUMBER | NO | This column is not supported and is NULL by default. |
| MAX_TRANS | NUMBER | NO | This column is not supported and is NULL by default. |
| INITIAL_EXTENT | NUMBER | NO | This column is not supported and is NULL by default. |
| NEXT_EXTENT | NUMBER | NO | This column is not supported and is NULL by default. |
| MIN_EXTENT | NUMBER | NO | This column is not supported and is NULL by default. |
| MAX_EXTENT | NUMBER | NO | This column is not supported and is NULL by default. |
| MAX_SIZE | NUMBER | NO | This column is not supported and is NULL by default. |
| PCT_INCREASE | NUMBER | NO | This column is not supported and is NULL by default. |
| FREELISTS | NUMBER | NO | This column is not supported and is NULL by default. |
| FREELIST_GROUPS | NUMBER | NO | This column is not supported and is NULL by default. |
| LOGGING | VARCHAR2(7) | NO | This column is not supported and is NULL by default. |
| COMPRESSION | VARCHAR2(8) | NO | Indicates whether compression is enabled. |
| COMPRESS_FOR | VARCHAR2(30) | YES | The compression method. |
| NUM_ROWS | NUMBER | NO | This column is not supported and is NULL by default. |
| BLOCKS | NUMBER | NO | This column is not supported and is NULL by default. |
| EMPTY_BLOCKS | NUMBER | NO | This column is not supported and is NULL by default. |
| AVG_SPACE | NUMBER | NO | This column is not supported and is NULL by default. |
| CHAIN_CNT | NUMBER | NO | This column is not supported and is NULL by default. |
| AVG_ROW_LEN | NUMBER | NO | This column is not supported and is NULL by default. |
| SAMPLE_SIZE | NUMBER | NO | This column is not supported and is NULL by default. |
| LAST_ANALYZED | DATE | NO | This column is not supported and is NULL by default. |
| BUFFER_POOL | VARCHAR2(7) | NO | This column is not supported and is NULL by default. |
| FLASH_CACHE | VARCHAR2(7) | NO | This column is not supported and is NULL by default. |
| CELL_FLASH_CACHE | VARCHAR2(7) | NO | This column is not supported and is NULL by default. |
| GLOBAL_STATS | VARCHAR2(3) | NO | This column is not supported and is NULL by default. |
| USER_STATS | VARCHAR2(3) | NO | This column is not supported and is NULL by default. |
| IS_NESTED | VARCHAR2(3) | NO | This column is not supported and is NULL by default. |
| PARENT_TABLE_PARTITION | VARCHAR2(128) | NO | This column is not supported and is NULL by default. |
| INTERVAL | VARCHAR2(3) | NO | This column is not supported and is NULL by default. |
| SEGMENT_CREATED | VARCHAR2(4) | NO | This column is not supported and is NULL by default. |
| INDEXING | VARCHAR2(4) | NO | Indicates the index attribute. Valid values: |
| READ_ONLY | VARCHAR2(4) | NO | Indicates whether the subpartition is read-only: |
| INMEMORY | VARCHAR2(8) | NO | This column is compatible with Oracle and has a NULL value, which has no actual meaning. |
| INMEMORY_PRIORITY | VARCHAR2(8) | NO | This column is compatible with Oracle and has a NULL value, which has no actual meaning. |
| INMEMORY_DISTRIBUTE | VARCHAR2(15) | NO | This column is compatible with Oracle and has a NULL value, which has no actual meaning. |
| INMEMORY_COMPRESSION | VARCHAR2(17) | NO | This column is compatible with Oracle and has a NULL value, which has no actual meaning. |
| INMEMORY_DUPLICATE | VARCHAR2(13) | NO | This column is compatible with Oracle and has a NULL value, which has no actual meaning. |
| CELLMEMORY | VARCHAR2(24) | NO | This column is not supported and is NULL by default. |
| INMEMORY_SERVICE | VARCHAR2(12) | NO | This column is compatible with Oracle and has a NULL value, which has no actual meaning. |
| INMEMORY_SERVICE_NAME | VARCHAR2(100) | NO | This column is compatible with Oracle and has a NULL value, which has no actual meaning. |
| 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 partition information of the TBL2_F_RL table in the partitioned tables that the current user owns.
obclient [SYS]> SELECT TABLE_NAME, COMPOSITE, PARTITION_NAME, SUBPARTITION_COUNT, HIGH_VALUE_LENGTH, PARTITION_POSITION
FROM SYS.USER_TAB_PARTITIONS
WHERE TABLE_NAME='TBL2_F_RL';
The query result is as follows:
+------------+-----------+----------------+--------------------+-------------------+--------------------+
| TABLE_NAME | COMPOSITE | PARTITION_NAME | SUBPARTITION_COUNT | HIGH_VALUE_LENGTH | PARTITION_POSITION |
+------------+-----------+----------------+--------------------+-------------------+--------------------+
| TBL2_F_RL | YES | P0 | 3 | 3 | 1 |
| TBL2_F_RL | YES | P1 | 3 | 3 | 2 |
+------------+-----------+----------------+--------------------+-------------------+--------------------+
2 rows in set
References
Query the partition information of all partitioned tables in the current tenant: DBA_TAB_PARTITIONS
Query the partition information of all partitioned tables accessible to the current user: ALL_TAB_PARTITIONS
Query the subpartition details of all partitioned tables owned by the current user: USER_TAB_SUBPARTITIONS