Note
This view is available starting with V2.2.77.
Purpose
This view displays the partition information of all partitioned tables that the current user has access to.
Applicability
This view is available only in Oracle mode of OceanBase Database.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| TABLE_OWNER | VARCHAR2(128) | NO | The user who owns the partitioned table. |
| TABLE_NAME | VARCHAR2(128) | NO | The name of the partitioned table. |
| COMPOSITE | VARCHAR2(3) | NO | Indicates whether the partition is a composite partition. |
| PARTITION_NAME | VARCHAR2(128) | NO | The name of the partition. |
| SUBPARTITION_COUNT | NUMBER | NO | 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. 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(7) | NO | This column is not supported. The default value is NULL. |
| COMPRESSION | VARCHAR2(8) | NO | Indicates whether compression is enabled. |
| 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. |
| IS_NESTED | VARCHAR2(3) | NO | This column is not supported. The default value is NULL. |
| PARENT_TABLE_PARTITION | VARCHAR2(128) | NO | This column is not supported. The default value is NULL. |
| INTERVAL | VARCHAR2(3) | NO | This column is not supported. The default value of this column is NO. |
| SEGMENT_CREATED | VARCHAR2(4) | NO | This column is not supported. The default value of this column is NULL. |
| INDEXING | VARCHAR2(4) | NO | This column is not supported. The default value of this column is NULL. |
| READ_ONLY | VARCHAR2(4) | NO | This column is not supported. The default value of this column is NULL. |
| INMEMORY | VARCHAR2(8) | NO | This column is an Oracle-compatible column. The value of this column is NULL, which does not have a real meaning. |
| INMEMORY_PRIORITY | VARCHAR2(8) | NO | This column is an Oracle-compatible column. The value of this column is NULL, which does not have a real meaning. |
| INMEMORY_DISTRIBUTE | VARCHAR2(15) | NO | This column is not supported. The default value of this column is NULL. |
| INMEMORY_COMPRESSION | VARCHAR2(17) | NO | This column is an Oracle-compatible column. The value of this column is NULL, which does not have a real meaning. |
| INMEMORY_DUPLICATE | VARCHAR2(13) | NO | This column is an Oracle-compatible column. The value of this column is NULL, which does not have a real meaning. |
| CELLMEMORY | VARCHAR2(24) | NO | This column is an Oracle-compatible column. The value of this column is NULL, which does not have a real meaning. |
| INMEMORY_SERVICE | VARCHAR2(12) | NO | This column is an Oracle-compatible column. The value of this column is NULL, which does not have a real meaning. |
| INMEMORY_SERVICE_NAME | VARCHAR2(100) | NO | This column is an Oracle-compatible column. The value of this column is NULL, which does not have a real meaning. |
| MEMOPTIMIZE_READ | VARCHAR2(8) | NO | This column is not supported. The default value of this column is NULL. |
| MEMOPTIMIZE_WRITE | VARCHAR2(8) | NO | This column is not supported. The default value of this column is NULL. |
Sample query
Query the partition information of the TBL2_F_RL table in the partitioned tables that are accessible to the current user.
obclient [SYS]> SELECT TABLE_OWNER, TABLE_NAME, COMPOSITE, PARTITION_NAME, SUBPARTITION_COUNT, HIGH_VALUE_LENGTH, PARTITION_POSITION
FROM SYS.ALL_TAB_PARTITIONS
WHERE TABLE_NAME='TBL2_F_RL';
The query result is as follows:
+-------------+------------+-----------+----------------+--------------------+-------------------+--------------------+
| TABLE_OWNER | TABLE_NAME | COMPOSITE | PARTITION_NAME | SUBPARTITION_COUNT | HIGH_VALUE_LENGTH | PARTITION_POSITION |
+-------------+------------+-----------+----------------+--------------------+-------------------+--------------------+
| SYS | TBL2_F_RL | YES | P0 | 3 | 3 | 1 |
| SYS | 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 that are accessible to the current user: USER_TAB_PARTITIONS
Query the subpartition details of all partitioned tables that are accessible to the current user: ALL_TAB_SUBPARTITIONS