Index Monitoring

Setting up

Enable monitoring:

select 'alter index ' || owner || '.' || index_name || ' monitoring usage;'
  from dba_indexes
 where owner = '?'
   and index_type not in ('LOB');

Validate it has been enabled on all indexes (in case some were locked):

select index_name
  from user_indexes
 where index_type not in ('LOB')
minus
select index_name
   from v$object_usage;

Verify, make sure to connect as the owner of the objects you are monitoring:

select *
   from v$object_usage;

Querying

Total list

select *
  from v$object_usage
 where used = 'NO';

List of indexes with segment size (query won't work nicely for partitioned objects)

 
select u.index_name, u.table_name, round(s.bytes / 1048576) "MB", u.used
  from v$object_usage u
     , user_segments s
 where u.used = 'NO'
   and s.segment_name = u.index_name
 order by s.bytes desc;

List of indexes without segments (count of this query + previous query should equal the total)

select u.index_name, i.segment_created
  from v$object_usage u
     , user_indexes i
 where u.used = 'NO'
   and u.index_name not in (select segment_name from user_segments)
   and i.index_name = u.index_name;