TO FIND INDEX DETAILS

SELECT dba.status,
fnd.application_id,
fnd.table_id,
fnd.index_id,
fnd.index_name,
fnd.description
FROM fnd_indexes fnd,
dba_indexes dba
WHERE table_id =
(SELECT table_id
FROM fnd_tables
WHERE table_name LIKE UPPER ('&table_name')) and fnd.index_name = dba.
index_name;

Note: Enter the table_name for which you want to find out index details.