В версии Oracle 9i появилась возможность текущего контроля использования конкретных индексов.
Для этого нужно пропустить команду:

ALTER INDEX name_index MONITORING USAGE;

и, через время посмотреть результат в представлении SYS.V$OBJECT_USAGE. Там выносится вердикт для индекса : USED = yes / no

Для всех индексов пакет команд можно сформировать таким запросом:
SELECT 'ALTER INDEX '||index_name||' MONITORING USAGE;' FROM USER_INDEXES;

Для более старых версий Oracle, узнать об использовании индекса, хоть и обходным путём, но узнать всё же можно.

Вариант 1

Для этого нужно выбрать тексты всех SQL из совместно используемого пула :

Потом нужно прогнать все запросы через explain plan , сохранить результаты и выбрать из них нужную статистику. Например сведения о том какие таблицы и индексы вообще не упоминаются :)

Вариант 2

Можно переодически просматривать содержимое буферный кэш Oracle и выбирать там имеющиеся индексы используя неафишируемую табличку x$bh.

Для Oracle 7

SELECT DISTINCT d.segment_name, owner,d.SEGMENT_TYPE FROM x$bh x, DBA_EXTENTS d
WHERE x.dbafil=d.file_id AND d.block_id=x.DBABLK AND d.OWNER NOT IN ('SYS','SYSTEM')
AND d.OWNER='UTC'
AND d.SEGMENT_TYPE='INDEX'

И для Oracle 9 :

SELECT DISTINCT d.segment_name, owner,d.SEGMENT_TYPE, x.tch FROM x$bh x, DBA_EXTENTS d
WHERE x.FILE#=d.file_id AND d.block_id=x.DBABLK AND d.OWNER NOT IN ('SYS','SYSTEM')
AND d.OWNER='UTC'
AND segment_type='INDEX'

теоретически поле x.tch - это счётчик использования в буфере, или, скорее всего, счётчик изменений, хотя у меня он почему-то иногда не изменяетя...