Note
This view is available starting with V2.2.30.
Purpose
This view displays information about all index partitions owned by the current user in the database. It does not include the INDEX_OWNER column, unlike DBA_IND_SUBPARTITIONS and ALL_IND_SUBPARTITIONS.
Applicability
This view is available only in Oracle mode of OceanBase Database.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| INDEX_NAME | VARCHAR2(128) | NO | The name of the index. |
| PARTITION_NAME | VARCHAR2(128) | NO | The name of the partition. |
| SUBPARTITION_NAME | VARCHAR2(128) | NO | The name of the subpartition. |
| HIGH_VALUE | VARCHAR2(1024) | NO | The expression bound to the subpartition. |
| HIGH_VALUE_LENGTH | NUMBER | NO | The length of the expression bound to the subpartition. |
| PARTITION_POSITION | NUMBER | NO | The position of the partition in the index. |
| SUBPARTITION_POSITION | NUMBER | NO | The position of the subpartition in the partition. |
| STATUS | VARCHAR2(8) | NO | Indicates whether the index partition is available. |
| TABLESPACE_NAME | VARCHAR2(30) | NO | The name of the tablespace that contains the partition. |
| PCT_FREE | NUMBER | NO | This column is not supported. The value of this column is NULL. |
| INI_TRANS | NUMBER | NO | This column is not supported. The value of this column is NULL. |
| MAX_TRANS | NUMBER | NO | This column is not supported. The value of this column is NULL. |
| INITIAL_EXTENT | NUMBER | NO | This column is not supported. The value of this column is NULL. |
| NEXT_EXTENT | NUMBER | NO | This column is not supported. The value of this column is NULL. |
| MIN_EXTENT | NUMBER | NO | This column is not supported. The value of this column is NULL. |
| MAX_EXTENT | NUMBER | NO | This column is not supported. The value of this column is NULL. |
MAX_SIZE | NUMBER | NO | This field is not supported and is displayed as NULL. | | PCT_INCREASE | NUMBER | NO | This field is not supported and is displayed as NULL. | | FREELISTS | NUMBER | NO | This field is not supported and is displayed as NULL. | | FREELIST_GROUPS | NUMBER | NO | This field is not supported and is displayed as NULL. | | LOGGING | VARCHAR2(7) | NO | This field is not supported and is displayed as NULL. | | COMPRESSION | VARCHAR2(8) | NO | Indicates whether compression is enabled for the partitioned index:
The value is NULL. | | GLOBAL_STATS | VARCHAR2(3) | NO | This column is not supported at this time. The column value is displayed as NULL. | | INTERVAL | VARCHAR2(3) | NO | This column is not supported. This column is set to NO by default. | | SEGMENT_CREATED | VARCHAR2(3) | NO | This column is not supported and is displayed as NULL. | | PARAMETERS | VARCHAR2(1000) | NO | This column is unavailable and shows as NULL. | | DOMIDX_OPSTATUS | VARCHAR2(6) | NO | This column is not supported and displays as NULL. |
Sample query
Query the information about index subpartitions (subpartitions) in the databases owned by the current user.
obclient [SYS]> SELECT INDEX_NAME, PARTITION_NAME, SUBPARTITION_NAME, HIGH_VALUE, PARTITION_POSITION, SUBPARTITION_POSITION FROM USER_IND_SUBPARTITIONS;
The query result is as follows:
+----------------+----------------+-------------------+------------+--------------------+-----------------------+
| INDEX_NAME | PARTITION_NAME | SUBPARTITION_NAME | HIGH_VALUE | PARTITION_POSITION | SUBPARTITION_POSITION |
+----------------+----------------+-------------------+------------+--------------------+-----------------------+
| TBL2_F_RL_IDX1 | P0 | SP0 | 1,3 | 1 | 1 |
| TBL2_F_RL_IDX1 | P0 | SP1 | 4,6 | 1 | 2 |
| TBL2_F_RL_IDX1 | P0 | SP2 | 7,9 | 1 | 3 |
| TBL2_F_RL_IDX1 | P1 | SP3 | 1,3 | 2 | 1 |
| TBL2_F_RL_IDX1 | P1 | SP4 | 4,6 | 2 | 2 |
| TBL2_F_RL_IDX1 | P1 | SP5 | 7,9 | 2 | 3 |
+----------------+----------------+-------------------+------------+--------------------+-----------------------+
6 rows in set
References
Query the information about all index subpartitions in the current tenant: DBA_IND_SUBPARTITIONS
Query the information about all index subpartitions accessible to the current user: ALL_IND_SUBPARTITIONS