Note
This view is available starting with V2.2.30.
Purpose
The ALL_INDEXES view displays the index column information of indexes of all tables accessible to the current user.
Applicability
This view is available only in Oracle mode of OceanBase Database.
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. It is set to NULL by default. |
| TABLESPACE_NAME | VARCHAR2(30) | NO | The name of the tablespace that contains the index. |
| INI_TRANS | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| MAX_TRANS | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| INITIAL_EXTENT | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| NEXT_EXTENT | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| MIN_EXTENTS | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| MAX_EXTENTS | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| PCT_INCREASE | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| PCT_THRESHOLD | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| INCLUDE_COLUMN | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| FREELISTS | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| FREELIST_GROUPS | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| PCT_FREE | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| LOGGING | VARCHAR2(3) | NO | This column is not supported. It is set to NULL by default. |
| BLEVEL | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| LEAF_BLOCKS | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| DISTINCT_KEYS | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| AVG_LEAF_BLOCKS_PER_KEY | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| AVG_DATA_BLOCKS_PER_KEY | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| CLUSTERING_FACTOR | NUMBER | NO | This column is not supported. It 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. It is set to NULL by default. |
| SAMPLE_SIZE | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| LAST_ANALYZED | DATE | NO | This column is not supported. It is set to NULL by default. |
| DEGREE | VARCHAR2(40) | NO | This column is not supported. The default value of this column is NULL. |
| INSTANCES | VARCHAR2(40) | NO | This column is not supported. The default value of this column is NULL. |
| PARTITIONED | VARCHAR2(3) | NO | Indicates whether the index is a partitioned index. |
| TEMPORARY | VARCHAR2(1) | NO | This column is not supported. The default value of this column is NULL. |
| GENERATED | VARCHAR2(1) | NO | This column is not supported. The default value of this column is NULL. |
| SECONDARY | VARCHAR2(1) | NO | This column is not supported. The default value of this column is NULL. |
| BUFFER_POOL | VARCHAR2(7) | NO | This column is not supported. The default value of this column is NULL. |
| FLASH_CACHE | VARCHAR2(7) | NO | This column is not supported. The default value of this column is NULL. |
| CELL_FLASH_CACHE | VARCHAR2(7) | NO | This column is not supported. The default value of this column is NULL. |
| USER_STATS | VARCHAR2(3) | NO | This column is not supported. The default value of this column is NULL. |
| DURATION | VARCHAR2(15) | NO | This column is not supported. The default value of this column is NULL. |
| PCT_DIRECT_ACCESS | NUMBER | NO | This column is not supported. The default value of this column is NULL. |
| ITYP_OWNER | VARCHAR2(128) | NO | This column is not supported. The default value of this column is NULL. |
| ITYP_NAME | VARCHAR2(128) | NO | This column is not supported. The default value of this column is NULL. |
| PARAMETERS | VARCHAR2(1000) | NO | This column is not supported. The default value of this column is NULL. |
| GLOBAL_STATS | VARCHAR2(3) | NO | This column is not supported. The default value of this column is NULL. |
| DOMIDX_STATUS | VARCHAR2(12) | NO | This column is not supported. The default value of this column is NULL. |
| DOMIDX_OPSTATUS | VARCHAR2(6) | NO | This column is not supported. The default value of this column is NULL. |
| 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. The default value of this column is NULL. |
| DROPPED | VARCHAR2(3) | NO | Indicates whether the index is 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. The default value of this column is NULL. |
| SEGMENT_CREATED | VARCHAR2(3) | NO | This column is not supported. The default value of this column is NULL. |
| ORPHANED_ENTRIES | VARCHAR2(3) | NO | This column is not supported. The default value of this column is NULL. |
| INDEXING | VARCHAR2(7) | NO | This column is not supported. The default value of this column is NULL. |
| AUTO | VARCHAR2(3) | NO | This column is not supported. The default value of this column is NULL. |
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 indexes, see View indexes.