Note
This view is available starting with V2.2.30.
Purpose
The DBA_INDEXES view displays the index information of all tables in the database.
Applicability
This view is available only 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 not unique. |
| 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 in which the index resides. |
| 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 and is NULL by default. | | PCT_INCREASE | NUMBER | NO | This column is not supported and is NULL by default. | | PCT_THRESHOLD | NUMBER | NO | This column is not supported and is NULL by default. | | INCLUDE_COLUMN | NUMBER | NO | This column is not supported and is NULL by default. | | FREELISTS | NUMBER | NO | This column is not supported and is NULL by default. | | FREELIST_GROUPS | NUMBER | NO | This column is not supported and is NULL by default. | | PCT_FREE | NUMBER | NO | This column is not supported and is NULL by default. | | LOGGING | VARCHAR2(3) | NO | This column is not supported and is NULL by default. | | BLEVEL | NUMBER | NO | This column is not supported and is NULL by default. | | LEAF_BLOCKS | NUMBER | NO | This column is not supported and is NULL by default. | | DISTINCT_KEYS | NUMBER | NO | This column is not supported and is NULL by default. | | AVG_LEAF_BLOCKS_PER_KEY | NUMBER | NO | This column is not supported and is NULL by default. | | AVG_DATA_BLOCKS_PER_KEY | NUMBER | NO | This column is not supported and is NULL by default. | | CLUSTERING_FACTOR | NUMBER | NO | This column is not supported and is 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 NULL by default. | | SAMPLE_SIZE | NUMBER | NO | This column is not supported and is NULL by default. | | LAST_ANALYZED | DATE | NO | This column is not supported and is NULL by default. | | DEGREE
| VARCHAR2(40) | NO | This column is not supported and is NULL by default. | | INSTANCES | VARCHAR2(40) | NO | This column is not supported and is NULL by default. | | PARTITIONED | VARCHAR2(3) | NO | Indicates whether the index is a partitioned index. | | TEMPORARY | VARCHAR2(1) | NO | This column is not supported and is NULL by default. | | GENERATED | VARCHAR2(1) | NO | This column is not supported and is NULL by default. | | SECONDARY | VARCHAR2(1) | NO | This column is not supported and is NULL by default. | | 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 is NULL by default. | | CELL_FLASH_CACHE | VARCHAR2(7) | NO | This column is not supported and is NULL by default. | | USER_STATS | VARCHAR2(3) | NO | This column is not supported and is NULL by default. | | DURATION | VARCHAR2(15) | NO | This column is not supported and is NULL by default. | | PCT_DIRECT_ACCESS | NUMBER | NO | This column is not supported and is NULL by default. | | ITYP_OWNER | VARCHAR2(128) | NO | This column is not supported and is NULL by default. | | ITYP_NAME | VARCHAR2(128) | NO | This column is not supported and is NULL by default. | | PARAMETERS | VARCHAR2(1000) | NO | This column is not supported and is NULL by default. | | GLOBAL_STATS | VARCHAR2(3) | NO | This column is not supported and is NULL by default. | | DOMIDX_STATUS | VARCHAR2(12) | NO | This column is not supported and is NULL by default. | DOMIDX_OPSTATUS | VARCHAR2(6) | NO | Currently, this column is not supported and defaults to 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 | Currently, this field is not supported. By default, the value is NULL. | | DROPPED | VARCHAR2(3) | NO | Indicates whether the index is dropped into the recycle bin. | | VISIBILITY | VARCHAR2(9) | NO | Indicates whether the index is visible to the optimizer. | | DOMIDX_MANAGEMENT | VARCHAR2(14) | NO | This column is currently not supported, and it defaults to NULL. | | SEGMENT_CREATED | VARCHAR2(3) | NO | This column is not supported and is currently set to NULL by default. | | ORPHANED_ENTRIES | VARCHAR2(3) | NO | This field is currently not supported and defaults to NULL. |
Sample query
Query the index information of the TBL2 table in all tables of the database.
obclient [SYS]> SELECT * FROM SYS.DBA_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 accessible to the current user: ALL_INDEXES
For more information about index operations, see View indexes.