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 can access.
Applicability
This view is available only in Oracle-compatible 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. 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(7) | NO | This column is not supported. It is set to NULL by default. | ||||||||||
| 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. 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. | ||||||||||
| IS_NESTED | VARCHAR2(3) | NO | This column is not supported. It is set to NULL by default. | ||||||||||
| PARENT_TABLE_PARTITION | VARCHAR2(128) | 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 NO by default. |
||||||||||
| COLUMN_NAME | DATA_TYPE | NULLABLE | DESCRIPTION | --------------------- | ---------------- | ------------ | ------------------ | SEGMENT_CREATED | VARCHAR2(4) | NO | This column is not supported and is therefore NULL. | ||
| INDEXING | VARCHAR2(4) | NO | This field is not supported and will be NULL by default. | ||||||||||
| READ_ONLY | VARCHAR2(4) | NO | This column is not supported. The default value is NULL. | ||||||||||
| INMEMORY | VARCHAR2(8) | NO | This column is compatible with Oracle. The value is NULL and has no practical meaning. | ||||||||||
| INMEMORY_PRIORITY | VARCHAR2(8) | NO | This column is compatible with Oracle and its value is NULL, which has no practical meaning. | ||||||||||
| INMEMORY_DISTRIBUTE | VARCHAR2(15) | NO | This column is currently not supported and is default NULL | ||||||||||
| INMEMORY_COMPRESSION | VARCHAR2(17) | NO | A column that is compatible with Oracle, and whose value is NULL and does not have any practical meaning | ||||||||||
| INMEMORY_DUPLICATE | VARCHAR2(13) | NO | This field is for compatibility with Oracle. The value is NULL. | ||||||||||
| CELLMEMORY | VARCHAR2(24) | NO | A Oracle-compliant field. The value is NULL and does not carry any actual significance. | ||||||||||
| INMEMORY_SERVICE | VARCHAR2(12) | NO | Incompatible with Oracle. The value is NULL, and has no real meaning. | ||||||||||
| INMEMORY_SERVICE_NAME | VARCHAR2(100) | NO | Reserved for Oracle compatibility. The value is NULL. | ||||||||||
| MEMOPTIMIZE_READ | VARCHAR2(8) | NO | This column is not supported. The default value is NULL. | ||||||||||
| MEMOPTIMIZE_WRITE | VARCHAR2(8) | NO | This column is not supported. It will be NULL by default. |
Sample query
Query the primary partition information of the TBL2_F_RL table in the partitioned tables 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 primary partition information of all partitioned tables in the current tenant: DBA_TAB_PARTITIONS
Query the primary partition information of all partitioned tables owned by the current user: USER_TAB_PARTITIONS
Query the detailed information about subpartitions of all partitioned tables accessible to the current user: ALL_TAB_SUBPARTITIONS