Note
This view is available starting with V2.2.30.
Purpose
The ALL_INDEXES view displays information about the index columns of all tables that are accessible to the current user.
Applicability
This view is available only in OceanBase Database in Oracle mode.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| OWNER | VARCHAR2(128) | NO | The owner of the index. |
| INDEX_NAME | VARCHAR2(128) | NO | The name of the index. |
| INDEX_TYPE | VARCHAR2(27) | NO | The type of the index. |
| TABLE_OWNER | VARCHAR2(128) | NO | The owner of the object being indexed. |
| TABLE_NAME | VARCHAR2(128) | NO | The name of the object being indexed. |
| TABLE_TYPE | CHAR(5) | NO | The type of the object being indexed. |
| UNIQUENESS | VARCHAR2(9) | NO | Indicates whether the index is unique (UNIQUE) or nonunique. |
| COMPRESSION | VARCHAR2(13) | NO | Indicates whether the index is compressed. |
| PREFIX_LENGTH | NUMBER | NO | This column is not supported. The value of this column is NULL by default. |
| TABLESPACE_NAME | VARCHAR2(30) | NO | The name of the tablespace in which the index is stored. |
| INI_TRANS | NUMBER | NO | This column is not supported. The value of this column is NULL by default. |
| MAX_TRANS | NUMBER | NO | This column is not supported. The value of this column is NULL by default. |
| INITIAL_EXTENT | NUMBER | NO | This column is not supported. The value of this column is NULL by default. |
| NEXT_EXTENT | NUMBER | NO | This column is not supported. The value of this column is NULL by default. |
| MIN_EXTENTS | NUMBER | NO | This column is not supported. The value of this column is NULL by default. |
| MAX_EXTENTS | NUMBER | NO | This column is not supported and is set to NULL by default. |
| PCT_INCREASE | NUMBER | NO | This column is not supported and is set to NULL by default. |
| PCT_THRESHOLD | NUMBER | NO | This column is not supported and is set to NULL by default. |
| INCLUDE_COLUMN | NUMBER | NO | This column is not supported and is set to NULL by default. |
| FREELISTS | NUMBER | NO | This column is not supported and is set to NULL by default. |
| FREELIST_GROUPS | NUMBER | NO | This column is not supported and is set to NULL by default. |
| PCT_FREE | NUMBER | NO | This column is not supported and is set to NULL by default. |
| LOGGING | VARCHAR2(3) | NO | This column is not supported and is set to NULL by default. |
| BLEVEL | NUMBER | NO | This column is not supported and is set to NULL by default. |
| LEAF_BLOCKS | NUMBER | NO | This column is not supported and is set to NULL by default. |
| DISTINCT_KEYS | NUMBER | NO | This column is not supported and is set to NULL by default. |
| AVG_LEAF_BLOCKS_PER_KEY | NUMBER | NO | This column is not supported and is set to NULL by default. |
| AVG_DATA_BLOCKS_PER_KEY | NUMBER | NO | This column is not supported and is set to NULL by default. |
| CLUSTERING_FACTOR | NUMBER | NO | This column is not supported and is set to NULL by default. |
| STATUS | VARCHAR2(8) | NO | Indicates whether the index is VALID or UNUSABLE. |
| NUM_ROWS | NUMBER | NO | This column is not supported and is set to NULL by default. |
| SAMPLE_SIZE | NUMBER | NO | This column is not supported and is set to NULL by default. |
| LAST_ANALYZED | DATE | NO | This column is not supported and is set to NULL by default. |
| DEGREE | VARCHAR2(40) | NO | This column is not supported and is set to NULL by default. |
| INSTANCES | VARCHAR2(40) | NO | Currently, the field is not supported, and the field defaults to NULL. |
| PARTITIONED | VARCHAR2(3) | NO | Indicates whether the index is a partitioned index. |
| TEMPORARY | VARCHAR2(1) | NO | This column is not supported. Its default value is NULL. |
| GENERATED | VARCHAR2(1) | NO | This column is not supported. Its default value is NULL. |
| SECONDARY | VARCHAR2(1) | NO | This field is not supported. Its default value is NULL. |
| BUFFER_POOL | VARCHAR2(7) | NO | This column is not supported and is NULL by default. |
| FLASH_CACHE | VARCHAR2(7) | NO | This column is not supported, and its default value is NULL. |
| CELL_FLASH_CACHE | VARCHAR2(7) | NO | This column is currently not supported and defaults to NULL. |
| USER_STATS | VARCHAR2(3) | NO | This column is not supported and currently defaults to NULL. |
| DURATION | VARCHAR2(15) | NO | This column is not supported and is NULL by default. |
| PCT_DIRECT_ACCESS | NUMBER | NO | This column is currently not supported, and it is default NULL. |
| ITYP_OWNER | VARCHAR2(128) | NO | This column is not supported. It is currently NULL by default. |
| ITYP_NAME | VARCHAR2(128) | NO | This column is not supported and returns NULL by default. |
| PARAMETERS | VARCHAR2(1000) | NO | This column is not supported. Its default value is NULL. |
| GLOBAL_STATS | VARCHAR2(3) | NO | The GLOBAL_STATS field is not supported at this time. The value is set to NULL by default. |
| DOMIDX_STATUS | VARCHAR2(12) | NO | This column is not supported. It is NULL by default. |
| DOMIDX_OPSTATUS | VARCHAR2(6) | NO | This column is not supported. It is NULL by default. |
| FUNCIDX_STATUS | VARCHAR2(8) | NO | The status of the function-based index. |
| JOIN_INDEX | VARCHAR2(3) | NO | Indicates whether the index is a join index. |
| IOT_REDUNDANT_PKEY_ELIM | VARCHAR2(3) | NO | This column is not supported. It is NULL by default. |
| DROPPED | VARCHAR2(3) | NO | Indicates whether the index has been dropped and moved to the recycle bin. |
| VISIBILITY | VARCHAR2(9) | NO | Indicates whether the index is visible to the optimizer. |
| DOMIDX_MANAGEMENT | VARCHAR2(14) | NO | This column is not supported. It is NULL by default. |
| SEGMENT_CREATED | VARCHAR2(3) | NO | This column is not supported. It is NULL by default. |
| ORPHANED_ENTRIES | VARCHAR2(3) | NO | This column is not supported. It is NULL by default. |
| INDEXING | VARCHAR2(7) | NO | This column is not supported. It is NULL by default. |
| AUTO | VARCHAR2(3) | NO | This column is not supported. It is NULL by default. |
Sample query
Query the index information of the TBL2 table in the tables accessible to the current user.
obclient [SYS]> SELECT * FROM SYS.ALL_INDEXES WHERE table_name='TBL2';
The query result is as follows:
+-------+------------+------------+-------------+------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+------------------+----------+------+
| OWNER | INDEX_NAME | INDEX_TYPE | TABLE_OWNER | TABLE_NAME | TABLE_TYPE | UNIQUENESS | COMPRESSION | PREFIX_LENGTH | TABLESPACE_NAME | INI_TRANS | MAX_TRANS | INITIAL_EXTENT | NEXT_EXTENT | MIN_EXTENTS | MAX_EXTENTS | PCT_INCREASE | PCT_THRESHOLD | INCLUDE_COLUMN | FREELISTS | FREELIST_GROUPS | PCT_FREE | LOGGING | BLEVEL | LEAF_BLOCKS | DISTINCT_KEYS | AVG_LEAF_BLOCKS_PER_KEY | AVG_DATA_BLOCKS_PER_KEY | CLUSTERING_FACTOR | STATUS | NUM_ROWS | SAMPLE_SIZE | LAST_ANALYZED | DEGREE | INSTANCES | PARTITIONED | TEMPORARY | GENERATED | SECONDARY | BUFFER_POOL | FLASH_CACHE | CELL_FLASH_CACHE | USER_STATS | DURATION | PCT_DIRECT_ACCESS | ITYP_OWNER | ITYP_NAME | PARAMETERS | GLOBAL_STATS | DOMIDX_STATUS | DOMIDX_OPSTATUS | FUNCIDX_STATUS | JOIN_INDEX | IOT_REDUNDANT_PKEY_ELIM | DROPPED | VISIBILITY | DOMIDX_MANAGEMENT | SEGMENT_CREATED | ORPHANED_ENTRIES | INDEXING | AUTO |
+-------+------------+------------+-------------+------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+------------------+----------+------+
| SYS | TBL2_IDX1 | NORMAL | SYS | TBL2 | TABLE | NONUNIQUE | ENABLED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | VALID | NULL | NULL | NULL | 1 | NULL | NO | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NO | NULL | NO | VISIBLE | NULL | NULL | NULL | NULL | NULL |
+-------+------------+------------+-------------+------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+------------------+----------+------+
1 row in set
References
Query the index information of all tables owned by the current user: USER_INDEXES
Query the index information of all tables in the current tenant: DBA_INDEXES
For more information about index operations, see View indexes.