Note
This view is available starting with V2.2.77.
Purpose
The USER_PART_TABLES view displays the partitioning information of all partitioned tables owned by the current user.
Applicability
This view applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL 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. Valid values:
|
| SUBPARTITIONING_TYPE | VARCHAR2(9) | NO | The subpartitioning method. Valid values: |
| PARTITION_COUNT | NUMBER | NO | The number of partitions in the partitioned table. |
| DEF_SUBPARTITION_COUNT | NUMBER | NO | The default number of subpartitions if the table is a composite-partitioned table. |
| PARTITIONING_KEY_COUNT | NUMBER | NO | The number of partitioning keys. |
| SUBPARTITIONING_KEY_COUNT | NUMBER | NO | The number of subpartitioning keys if the table is a composite-partitioned table. |
| STATUS | VARCHAR2(8) | NO | At present, this column is not supported and its value is NULL by default. |
| DEF_TABLESPACE_NAME | VARCHAR2(30) | NO | The name of the tablespace containing the partitioned table. |
| DEF_PCT_FREE | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| DEF_PCT_USED | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| DEF_INI_TRANS | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| DEF_MAX_TRANS | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| DEF_INITIAL_EXTENT | VARCHAR2(40) | NO | At present, this column is not supported and its value is NULL by default. |
| DEF_NEXT_EXTENT | VARCHAR2(40) | NO | At present, this column is not supported and its value is NULL by default. |
| DEF_MIN_EXTENT | VARCHAR2(40) | NO | At present, this column is not supported and its value is NULL by default. |
| MAX_EXTENT | VARCHAR2(40) | NO | At present, this column is not supported and its value is NULL by default. |
| DEF_MAX_SIZE | VARCHAR2(40) | NO | At present, this column is not supported and its value is NULL by default. |
| DEF_PCT_INCREASE | VARCHAR2(40) | NO | At present, this column is not supported and its value is NULL by default. |
| DEF_FREELISTS | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| DEF_FREELIST_GROUPS | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| DEF_LOGGING | VARCHAR2(7) | NO | At present, this column is not supported and its value 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 | At present, this column is not supported and its value is NULL by default. |
| DEF_FLASH_CACHE | VARCHAR2(7) | NO | At present, this column is not supported and its value is NULL by default. |
| DEF_CELL_FLASH_CACHE | VARCHAR2(7) | NO | At present, this column is not supported and its value is NULL by default. |
| REF_PTN_CONSTRAINT_NAME | VARCHAR2(128) | NO | At present, this column is not supported and its value is NULL by default. |
| INTERVAL | VARCHAR2(1000) | NO | At present, this column is not supported and its value is NULL by default. |
| AUTOLIST | VARCHAR2(3) | NO | Indicates whether the local index is partitioned through the automatic list. Valid values: |
| INTERVAL_SUBPARTITION | VARCHAR2(1000) | NO | At present, this column is not supported and its value is NULL by default. |
| AUTOLIST_SUBPARTITION | VARCHAR2(3) | NO | At present, this column is not supported and its value is NULL by default. |
| IS_NESTED | VARCHAR2(3) | NO | At present, this column is not supported and its value is NULL by default. |
| DEF_SEGMENT_CREATED | VARCHAR2(4) | NO | At present, this column is not supported and its value is NULL by default. |
| DEF_INDEXING | VARCHAR2(3) | NO | The index attribute specified for the table. Valid values: ON-INDEXING: ON is explicitly specified, or the index attribute is not specified.OFF-INDEXING: The index attribute is disabled. |
| DEF_INMEMORY | VARCHAR2(8) | NO | This column is used for compatibility with Oracle. The default value of this column is NULL, which has no actual meaning. |
| DEF_INMEMORY_PRIORITY | VARCHAR2(8) | NO | This column is used for compatibility with Oracle. The default value of this column is NULL, which has no actual meaning. |
| DEF_INMEMORY_DISTRIBUTE | VARCHAR2(15) | NO | This column is used for compatibility with Oracle. The default value of this column is NULL, which has no actual meaning. |
| DEF_INMEMORY_COMPRESSION | VARCHAR2(17) | NO | This column is used for compatibility with Oracle. The default value of this column is NULL, which has no actual meaning. |
| DEF_INMEMORY_DUPLICATE | VARCHAR2(13) | NO | This column is used for compatibility with Oracle. The default value of this column is NULL, which has no actual meaning. |
| DEF_READ_ONLY | VARCHAR2(3) | NO | The default settings for new partitions. Valid values: YES: New partitions are read-only.NO: Read/write operations are supported for new partitions. |
| DEF_CELLMEMORY | VARCHAR2(24) | NO | This column is used for compatibility with Oracle. The default value of this column is NULL, which has no actual meaning. |
| DEF_INMEMORY_SERVICE | VARCHAR2(12) | NO | This column is used for compatibility with Oracle. The default value of this column is NULL, which has no actual meaning. |
| DEF_INMEMORY_SERVICE_NAME | VARCHAR2(1000) | NO | This column is used for compatibility with Oracle. The default value of this column is NULL, which has no actual meaning. |
| AUTO | VARCHAR2(3) | NO | Indicates whether the table is partitioned automatically. Valid values: |
Sample query
Query the partition information of the TBL2_F_RL table in the partition 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