Unused Indexes – With constraints

SELECT i.owner,
         a.index_name,
         a.table_name,
         c.constraint_name,
         a.monitoring,
         a.used,
         a.start_monitoring,
         ROUND (SUM (b.bytes) / 1024 / 1024 / 1024) GB,
         ROUND (SUM (b.bytes) / 1024 / 1024) MB,
         i.tablespace_name
    FROM all_object_usage a,
         dba_segments b,
         dba_indexes i,
         dba_constraints c
   WHERE     a.index_name = b.segment_name
         AND a.used = 'NO'
         AND b.segment_name = i.index_name
         AND a.table_name = c.table_name(+)
GROUP BY i.owner,
         a.index_name,
         a.table_name,
         c.constraint_name,
         a.monitoring,
         a.used,
         a.start_monitoring,
         i.tablespace_name
ORDER BY ROUND (SUM (b.bytes) / 1024 / 1024 / 1024) DESC
 
and
 
SELECT i.tablespace_name, round(SUM (b.bytes) / 1024 / 1024 / 1024) GB
  FROM all_object_usage a, dba_segments b, dba_indexes i
 WHERE     a.index_name = b.segment_name
       AND a.used = 'NO'
       AND b.segment_name = i.index_name
       group by i.tablespace_name;
 
or
 
SELECT i.tablespace_name, round(SUM (b.bytes) / 1024 / 1024 / 1024) GB
  FROM all_object_usage a, dba_segments b, dba_indexes i
 WHERE     a.index_name = b.segment_name
       AND a.used = 'NO'
       AND b.segment_name = i.index_name
       group by i.tablespace_name;