Note
This view is available starting with V2.2.77.
Purpose
This view displays the partition information of all partitions of all indexes that the current user can access.
Applicability
This view is available only in Oracle mode.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| OWNER | VARCHAR2(128) | NO | The owner of the partitioned index. |
| INDEX_NAME | VARCHAR2(128) | NO | The name of the partitioned index. |
| TABLE_NAME | VARCHAR2(128) | NO | The name of the table to which the index belongs. |
| 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 index. |
| DEF_SUBPARTITION_COUNT | NUMBER | NO | For a composite partitioned index, the number of subpartitions. |
| PARTITIONING_KEY_COUNT | NUMBER | NO | The number of partitioning keys. |
| SUBPARTITIONING_KEY_COUNT | NUMBER | NO | For a composite partitioned index, the number of subpartitioning keys. |
| LOCALITY | VARCHAR2(6) | NO | Indicates whether the partitioned index is LOCAL or GLOBAL. |
| ALIGNMENT | VARCHAR2(12) | NO | Indicates whether the partitioned index is PREFIXED or NON_PREFIXED. |
| DEF_TABLESPACE_NAME | VARCHAR2(30) | NO | For a LOCAL index, the default tablespace when a table partition is added or split. |
| DEF_PCT_FREE | NUMBER | NO | This column is not supported. The default value is 0. |
| DEF_INI_TRANS | NUMBER | NO | This column is not supported. The default value is 0. |
| DEF_MAX_TRANS | NUMBER | NO | This column is not supported. The default value is 0. |
| DEF_INITIAL_EXTENT | VARCHAR2(40) | NO | This column is not supported. The default value is NULL. |
| DEF_NEXT_EXTENT | VARCHAR2(40) | NO | This column is not supported. The default value is NULL. |
| DEF_MIN_EXTENTS | VARCHAR2(40) | NO | This column is not supported. The default value is NULL. |
| DEF_MAX_EXTENTS | VARCHAR2(40) | NO | This column is not supported. The default value is NULL. |
| DEF_MAX_SIZE | VARCHAR2(40) | NO | This column is not supported. The default value is NULL. |
| DEF_PCT_INCREASE | VARCHAR2(40) | NO | This column is not supported. The default value is NULL. |
| DEF_FREELISTS | NUMBER | NO | This column is not supported. The default value is 0. |
| DEF_FREELIST_GROUPS | NUMBER | NO | This column is not supported. The default value is 0. |
| DEF_LOGGING | VARCHAR2(7) | NO | This column is not supported. The default value is NULL. |
| DEF_BUFFER_POOL | VARCHAR2(7) | NO | This column is not supported. The default value is NULL. |
| DEF_FLASH_CACHE | VARCHAR2(7) | NO | This column is not supported. The default value is NULL. |
| DEF_CELL_FLASH_CACHE | VARCHAR2(7) | NO | This column is not supported. The default value is NULL. |
| DEF_PARAMETERS | VARCHAR2(1000) | NO | This column is not supported. The default value is NULL. |
| INTERVAL | VARCHAR2(1000) | NO | This column is not supported. The default value is NO. |
| AUTOLIST | VARCHAR2(3) | NO | Indicates whether the LOCAL index is automatically list-partitioned: |
| INTERVAL_SUBPARTITION | VARCHAR2(1000) | NO | This column is not supported. The default value is NULL. |
| AUTOLIST_SUBPARTITION | VARCHAR2(1000) | NO | This column is not supported. The default value is NULL. |
Sample query
Query the partition information of the partition index TBL1_H_IDX1 that is accessible to the current user.
obclient [SYS]> SELECT * FROM SYS.ALL_PART_INDEXES WHERE INDEX_NAME='TBL1_H_IDX1'\G
The query result is as follows:
*************************** 1. row ***************************
OWNER: SYS
INDEX_NAME: TBL1_H_IDX1
TABLE_NAME: TBL1_H
PARTITIONING_TYPE: RANGE
SUBPARTITIONING_TYPE: NONE
PARTITION_COUNT: 3
DEF_SUBPARTITION_COUNT: 0
PARTITIONING_KEY_COUNT: 1
SUBPARTITIONING_KEY_COUNT: 0
LOCALITY: GLOBAL
ALIGNMENT: PREFIXED
DEF_TABLESPACE_NAME: NULL
DEF_PCT_FREE: 0
DEF_INI_TRANS: 0
DEF_MAX_TRANS: 0
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: 0
DEF_FREELIST_GROUPS: 0
DEF_LOGGING: NULL
DEF_BUFFER_POOL: NULL
DEF_FLASH_CACHE: NULL
DEF_CELL_FLASH_CACHE: NULL
DEF_PARAMETERS: NULL
INTERVAL: NO
AUTOLIST: NO
INTERVAL_SUBPARTITION: NULL
AUTOLIST_SUBPARTITION: NULL
1 row in set
References
Query the partition information of all partition indexes in the current tenant: DBA_PART_INDEXES
Query the partition information of all partition indexes owned by the current user: USER_PART_INDEXES