JAVA J2EE 4 U

 SET LINESIZE 200

SET PAGESIZE 100

COLUMN index_name FORMAT A30

COLUMN table_name FORMAT A30

COLUMN used FORMAT A4

COLUMN segment_size_mb FORMAT 999999.99

COLUMN columns FORMAT A40

SELECT 

    iu.index_name,

    iu.table_name,

    iu.used,

    ROUND(s.bytes/1024/1024,2) AS segment_size_mb,

    LISTAGG(ic.column_name, ', ') 

      WITHIN GROUP (ORDER BY ic.column_position) AS columns

FROM 

    v$object_usage iu

  JOIN dba_indexes i 

    ON iu.index_name = i.index_name 

   AND iu.table_name = i.table_name 

   AND i.owner = SYS_CONTEXT('USERENV','CURRENT_SCHEMA')

  JOIN dba_ind_columns ic 

    ON ic.index_name = iu.index_name 

   AND ic.table_name = iu.table_name 

   AND ic.index_owner = i.owner

  LEFT JOIN dba_segments s 

    ON s.segment_name = iu.index_name 

   AND s.owner = i.owner 

   AND s.segment_type = 'INDEX'

WHERE 

    iu.used = 'NO'

GROUP BY 

    iu.index_name, iu.table_name, iu.used, s.bytes

ORDER BY 

    segment_size_mb DESC;