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 privileges on.
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. |
| PARTITIONING_TYPE | VARCHAR2(9) | NO | The partitioning method:
|
| SUBPARTITIONING_TYPE | VARCHAR2(9) | NO | The subpartitioning method: |
| PARTITION_COUNT | NUMBER | NO | The number of partitions in the partitioned table. |
| DEF_SUBPARTITION_COUNT | NUMBER | NO | For a composite partitioned table, the number of subpartitions. |
| PARTITIONING_KEY_COUNT | NUMBER | NO | The number of partitioning keys. |
| SUBPARTITIONING_KEY_COUNT | NUMBER | NO | For a composite partitioned table, the number of subpartitioning keys. |
| STATUS | VARCHAR2(8) | NO | This column is not supported. The value of this column is NULL by default. |
| DEF_TABLESPACE_NAME | VARCHAR2(30) | NO | The name of the tablespace to which the partitioned table belongs. |
| DEF_PCT_FREE | NUMBER | NO | This column is not supported. The value of this column is NULL by default. |
| DEF_PCT_USED | NUMBER | NO | This column is not supported. The value of this column is NULL by default. |
| DEF_INI_TRANS | NUMBER | NO | This column is not supported. The value of this column is NULL by default. |
| DEF_MAX_TRANS | NUMBER | NO | This column is not supported. The value of this column is NULL by default. |
| DEF_INITIAL_EXTENT | VARCHAR2(40) | NO | This column is not supported. The value of this column is NULL by default. |
| DEF_NEXT_EXTENT | VARCHAR2(40) | NO | This column is not supported. The value of this column is NULL by default. |
| DEF_MIN_EXTENT | VARCHAR2(40) | NO | This column is not supported. The value of this column is NULL by default. |
| MAX_EXTENT | VARCHAR2(40) | NO | This column is not supported. The value of this column is NULL by default. |
| DEF_MAX_SIZE | VARCHAR2(40) | NO | This column is not supported. The value of this column is NULL by default. |
| DEF_PCT_INCREASE | VARCHAR2(40) | NO | This column is not supported. The value of this column is NULL by default. |
| DEF_FREELISTS | NUMBER | NO | This column is not supported. The value of this column is NULL by default. |
| DEF_FREELIST_GROUPS | NUMBER | NO | This column is not supported. The value of this column is NULL by default. |
| DEF_LOGGING | VARCHAR2(7) | NO | This column is not supported. It is NULL by default. |
| DEF_COMPRESSION | VARCHAR2(8) | NO | Indicates whether compression is enabled. |
| DEF_COMPRESS_FOR | VARCHAR2(30) | NO | The compression method. |
| DEF_BUFFER_POOL | VARCHAR2(7) | NO | This column is not supported. It is NULL by default. |
| DEF_FLASH_CACHE | VARCHAR2(7) | NO | This column is not supported. It is NULL by default. |
| DEF_CELL_FLASH_CACHE | VARCHAR2(7) | NO | This column is not supported. It is NULL by default. |
| REF_PTN_CONSTRAINT_NAME | VARCHAR2(128) | NO | This column is not supported. It is NULL by default. |
| INTERVAL | VARCHAR2(1000) | NO | This column is not supported. It is NULL by default. |
| AUTOLIST | VARCHAR2(3) | NO | Indicates whether the local index is automatically partitioned by list: |
| INTERVAL_SUBPARTITION | VARCHAR2(1000) | NO | This column is not supported. It is NULL by default. |
| AUTOLIST_SUBPARTITION | VARCHAR2(3) | NO | This column is not supported. It is NULL by default. |
| IS_NESTED | VARCHAR2(3) | NO | This column is not supported. It is NULL by default. |
| DEF_SEGMENT_CREATED | VARCHAR2(4) | NO | This column is not supported. It is NULL by default. |
| DEF_INDEXING | VARCHAR2(3) | NO | Indicates the index attribute specified for the table. Valid values: |
| DEF_INMEMORY | VARCHAR2(8) | NO | This column is an Oracle-compatible column. Its value is NULL and has no practical meaning. |
| DEF_INMEMORY_PRIORITY | VARCHAR2(8) | NO | This column is an Oracle-compatible column. Its value is NULL and has no practical meaning. |
| DEF_INMEMORY_DISTRIBUTE | VARCHAR2(15) | NO | This column is an Oracle-compatible column. Its value is NULL and has no practical meaning. |
| DEF_INMEMORY_COMPRESSION | VARCHAR2(17) | NO | This column is an Oracle-compatible column. Its value is NULL and has no practical meaning. |
| DEF_INMEMORY_DUPLICATE | VARCHAR2(13) | NO | This column is an Oracle-compatible column. Its value is NULL and has no practical meaning. |
| DEF_READ_ONLY | VARCHAR2(3) | NO | Indicates the default setting for new partitions: |
| DEF_CELLMEMORY | VARCHAR2(24) | NO | This column is an Oracle-compatible column. Its value is NULL and has no practical meaning. |
| DEF_INMEMORY_SERVICE | VARCHAR2(12) | NO | This column is an Oracle-compatible column. Its value is NULL and has no practical meaning. |
| DEF_INMEMORY_SERVICE_NAME | VARCHAR2(1000) | NO | This column is an Oracle-compatible column. Its value is NULL and has no practical meaning. |
| AUTO | VARCHAR2(3) | NO | Indicates whether the table is automatically partitioned: |
Sample query
Query the partition information of the TBL2_F_RL table in the partitioned tables owned by the current user.
obclient [SYS]> SELECT * FROM SYS.USER_PART_TABLES WHERE TABLE_NAME='TBL2_F_RL'\G
The query result is as follows:
*************************** 1. row ***************************
TABLE_NAME: TBL2_F_RL
PARTITIONING_TYPE: RANGE
SUBPARTITIONING_TYPE: LIST
PARTITION_COUNT: 2
DEF_SUBPARTITION_COUNT: 1
PARTITIONING_KEY_COUNT: 1
SUBPARTITIONING_KEY_COUNT: 1
STATUS: NULL
DEF_TABLESPACE_NAME: NULL
DEF_PCT_FREE: NULL
DEF_PCT_USED: NULL
DEF_INI_TRANS: NULL
DEF_MAX_TRANS: NULL
DEF_INITIAL_EXTENT: NULL
DEF_NEXT_EXTENT: NULL
DEF_MIN_EXTENTS: NULL
DEF_MAX_EXTENTS: NULL
DEF_MAX_SIZE: NULL
DEF_PCT_INCREASE: NULL
DEF_FREELISTS: NULL
DEF_FREELIST_GROUPS: NULL
DEF_LOGGING: NULL
DEF_COMPRESSION: ENABLED
DEF_COMPRESS_FOR: zstd_1.3.8
DEF_BUFFER_POOL: NULL
DEF_FLASH_CACHE: NULL
DEF_CELL_FLASH_CACHE: NULL
REF_PTN_CONSTRAINT_NAME: NULL
INTERVAL: NULL
AUTOLIST: NO
INTERVAL_SUBPARTITION: NULL
AUTOLIST_SUBPARTITION: NO
IS_NESTED: NULL
DEF_SEGMENT_CREATED: NULL
DEF_INDEXING: NULL
DEF_INMEMORY: NULL
DEF_INMEMORY_PRIORITY: NULL
DEF_INMEMORY_DISTRIBUTE: NULL
DEF_INMEMORY_COMPRESSION: NULL
DEF_INMEMORY_DUPLICATE: NULL
DEF_READ_ONLY: NULL
DEF_CELLMEMORY: NULL
DEF_INMEMORY_SERVICE: NULL
DEF_INMEMORY_SERVICE_NAME: NULL
AUTO: NO
1 row in set
References
- Query the partition information of all partitioned tables accessible to the current user: ALL_PART_TABLES
- Query the partition information of all partitioned tables in the current tenant: DBA_PART_TABLES