How to find unused indexes

To determine if your indexes are being used or you can drop them use index monitoring (this
feature is available from Oracle 9 onwards).

1. To start index monitoring:

SQL> alter index SAMPLE_INDEX monitoring usage;

If this statement fails with
ORA-00054: resource busy and acquire with NOWAIT specified
The index is already being used, so it doesn't have to be monitored.

It depends on your application how long you want to run monitoring. Are the same queries
running every day or are there batch runs once a month etc. This will determine duration of
monitoring. 

2. To see if the index is used or not query V$OBJECT_USAGE (you have to be logged in as the
schema owner of the index):

select * from V$OBJECT_USAGE where index_name = 'SAMPLE_INDEX';

3. To stop index monitoring:

SQL> alter index SAMPLE_INDEX nomonitoring usage;

4. If you decided to drop an index make sure you save DDL just in case.