Find unused indexes with MONITORING USAGE clause

Summary
Creating an index doesn't mean that the index will be used.
To find unused indexes for a period of time in Oracle9i and afterwards you must enable the INDEX MONITORING USAGE

Example
Create a table OBJECTS with an index:

CREATE TABLE OBJECTS AS SELECT * FROM ALL_OBJECTS;
CREATE INDEX OBJECTS_IDX ON OBJECTS(OBJECT_TYPE);

Query view to see if index is monitored:

SELECT * FROM V$OBJECT_USAGE;

No monitor is enabled. Enable index monitoring and check the view if started monitoring:

ALTER INDEX OBJECTS_IDX MONITORING USAGE;
SELECT * FROM V$OBJECT_USAGE;

Now use the index with the query:

SELECT COUNT(*) FROM OBJECTS
WHERE OBJECT_TYPE = 'TABLE';

Query again to see how monitor is going.

SELECT * FROM V$OBJECT_USAGE;

  

INDEX_NAME	TABLE_NAME	MON	USE	START_MONITORING
OBJECTS_IDX	OBJECTS	YES	YES	03/05/2008 11:43:55

To stop monitoring, execute:

ALTER INDEX OBJECTS_IDX NOMONITORING USAGE;

To enable and disable monitoring indexes for a specific schema, use the following 2 queries:

SELECT 'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' MONITORING USAGE;'
          MONITOR_INDEX_SQL
  FROM DBA_INDEXES
 WHERE OWNER = 'AP';

SELECT 'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' NOMONITORING USAGE;'
          MONITOR_INDEX_SQL
  FROM DBA_INDEXES
 WHERE OWNER = 'AP';

To drop the unused indexes (what is the meaning to support something useless?), execute:

SELECT 'DROP INDEX ' || I.OWNER || '.' || U.INDEX_NAME || ';' DROP_SQL
  FROM V$OBJECT_USAGE U, DBA_INDEXES I
 WHERE     USED = 'NO'
       AND U.INDEX_NAME = I.INDEX_NAME
       AND U.TABLE_NAME = I.TABLE_NAME;