SQL query to get Index Details on a Table

 SELECT

    ind.table_owner,
    ind.table_name,
    ind.index_name,
    LISTAGG(ind_col.column_name, ',') WITHIN GROUP(
    ORDER BY
        ind_col.column_position
    ) AS columns,
    col.data_type,
    col.data_length,
    ind.index_type,
    ind.uniqueness,
    ind_col.descend,
    ind.status
FROM
         all_indexes ind
    JOIN all_ind_columns ind_col ON ind.owner = ind_col.index_owner
                                    AND ind.index_name = ind_col.index_name
    JOIN all_tab_columns col ON col.owner = ind_col.index_owner
                                AND col.table_name = ind_col.table_name
                                AND col.column_name = ind_col.column_name
WHERE
    ind.table_name = 'EMP'
GROUP BY
    ind.table_owner,
    ind.table_name,
    ind.index_name,
    ind.index_type,
    ind.uniqueness,
    col.data_type,
    col.data_length,
    ind_col.descend,
    ind.status
ORDER BY
    ind.table_owner,
    ind.table_name;