Monitoring Index Usage
Oracle provides a means of monitoring indexes to determine if they are being used or not used. If it is determined that an index is not being used, then it can be dropped, thus eliminating unnecessary statement overhead.

To start monitoring an index's usage, issue this statement:

ALTER INDEX index MONITORING USAGE;

Later, issue the following statement to stop the monitoring:

ALTER INDEX index NOMONITORING USAGE;

The view V$OBJECT_USAGE can be queried for the index being monitored to see if the index has been used. The view contains a USED column whose value is YES or NO, depending upon if the index has been used within the time period being monitored. The view also contains the start and stop times of the monitoring period, and a MONITORING column (YES/NO) to indicate if usage monitoring is currently active.

Each time that you specify MONITORING USAGE, the V$OBJECT_USAGE view is reset for the specified index. The previous usage information is cleared or reset, and a new start time is recorded. When you specify NOMONITORING USAGE, no further monitoring is performed, and the end time is recorded for the monitoring period. Until the next ALTER INDEX ... MONITORING USAGE statement is issued, the view information is left unchanged.

column owner format a20 trunc
column segment_name format a30 trunc
select distinct b.owner, b.segment_name
  from x$bh a, dba_extents b
 where
 b.file_id=a.dbarfil and
 a.dbablk between b.block_id and b.block_id+blocks-1 and
 segment_type='INDEX' and
 b.owner not in ('SYS','SYSTEM')