You can query indexes by using system views, including USER_INDEXES, ALL_INDEXES, DBA_INDEXES, ALL_IND_COLUMNS, DBA_IND_COLUMNS, and USER_IND_COLUMNS.
Examples:
Query the indexes on all tables of a user.
obclient> SELECT * FROM USER_INDEXES WHERE table_name='TEST'; +-------------+------------+-------------+------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+ | 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 | +-------------+------------+-------------+------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+ | T5_NAME_IND | NORMAL | SYS | TEST | 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 | +-------------+------------+-------------+------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+ 1 row in setParameters:
TABLE_NAME: the name of the table that is queried, which isTESTin this example.TABLE_NAME: the name of the table that is queried, which isTESTin this example.INDEX_TYPE: the index type. The valueNORMALindicates a normal index.UNIQUENESS: indicates whether the index is a unique index. The valueNONUNIQUEindicates that this index is not a unique index.
Query all indexes on a table.
obclient> SELECT * FROM ALL_INDEXES WHERE table_name='TEST'; +-------+-------------+------------+-------------+------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+ | 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 | +-------+-------------+------------+-------------+------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+ | SYS | T5_NAME_IND | NORMAL | SYS | TEST | 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 | +-------+-------------+------------+-------------+------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+ 1 row in setParameters:
COMPRESSION: indicates whether index compression is enabled. The default value isENABLED.PREFIX_LENGTH: the number of fields compressed during index compression.INCLUDE_COLUMN: the ID of the last column of the primary key of the index-organized table.DISTINCT_KEYS: the number of unique index values.DROPPED: indicates whether the index is dropped and moved to the recycle bin.
Query the indexes on all tables in the current database.
obclient> SELECT * FROM DBA_INDEXES; +-------+---------------------------------------+------------+-------------+-------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+ | 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 | +-------+---------------------------------------+------------+-------------+-------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+ | SYS | TABLE_NAME1_OBPK_1639997002986248 | NORMAL | SYS | TABLE_NAME1 | TABLE | UNIQUE | 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 | +-------+---------------------------------------+------------+-------------+-------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+ 5 rows in setParameters:
OWNER: the current database user, which isSYSin this example.INDEX_TYPE: the index type. The valueNORMALindicates a normal index.UNIQUENESS: indicates whether the index is a unique index.
Query the indexed columns of indexes on all tables accessible to the current user.
bclient> SELECT * FROM ALL_IND_COLUMNS; +-------------+---------------------------------------+-------------+-------------+-------------+-----------------+---------------+-------------+---------+ | INDEX_OWNER | INDEX_NAME | TABLE_OWNER | TABLE_NAME | COLUMN_NAME | COLUMN_POSITION | COLUMN_LENGTH | CHAR_LENGTH | DESCEND | +-------------+---------------------------------------+-------------+-------------+-------------+-----------------+---------------+-------------+---------+ | SYS | TABLE_NAME1_OBPK_1639997002986248 | SYS | TABLE_NAME1 | W_ID | 1 | 22 | 0 | ASC | +-------------+---------------------------------------+-------------+-------------+-------------+-----------------+---------------+-------------+---------+ 12 rows in setParameters:
DESCEND: The valueASCindicates that the indexed columns are sorted in ascending order.COLUMN_NAME: the column name.
Query the indexed columns of indexes on all tables in the database.
obclient> SELECT * FROM DBA_IND_COLUMNS; +-------------+---------------------------------------+-------------+-------------+-------------+-----------------+---------------+-------------+---------+ | INDEX_OWNER | INDEX_NAME | TABLE_OWNER | TABLE_NAME | COLUMN_NAME | COLUMN_POSITION | COLUMN_LENGTH | CHAR_LENGTH | DESCEND | +-------------+---------------------------------------+-------------+-------------+-------------+-----------------+---------------+-------------+---------+ | SYS | TABLE_NAME1_OBPK_1639997002986248 | SYS | TABLE_NAME1 | W_ID | 1 | 22 | 0 | ASC | +-------------+---------------------------------------+-------------+-------------+-------------+-----------------+---------------+-------------+---------+ 12 rows in setParameters:
INDEX_OWNER: the index owner.INDEX_NAME: the index name.
Query the details of indexes on a table.
obclient> SELECT * FROM USER_IND_COLUMNS WHERE table_name='TEST'; +-------------+------------+-------------+-----------------+---------------+-------------+---------+ | INDEX_NAME | TABLE_NAME | COLUMN_NAME | COLUMN_POSITION | COLUMN_LENGTH | CHAR_LENGTH | DESCEND | +-------------+------------+-------------+-----------------+---------------+-------------+---------+ | T5_NAME_IND | TEST | NAME | 1 | 1 | 1 | ASC | +-------------+------------+-------------+-----------------+---------------+-------------+---------+ 1 row in setParameters:
COLUMN_POSITION: the column position.COLUMN_LENGTH: the column length.CHAR_LENGTH: the character length.