Query for Unused Index

SELECT owner,index_name, status
FROM all_indexes
WHERE owner NOT IN ('SYS', 'SYSTEM')
  AND status != 'VALID'
  AND
  (
   status != 'N/A'
   OR index_name IN
   (
    SELECT index_name
     FROM all_ind_partitions
     WHERE status != 'USABLE'
      AND
      (
       status != 'N/A'
       OR index_name IN
       (
        SELECT index_name
         FROM all_ind_subpartitions
         WHERE status != 'USABLE'
       )
      )
   )
  );