HOW TO FIND UNUSED INDEXES

Enable monitoring on an index:

ALTER INDEX index_name MONITORING USAGE;

Monitoring Query:

SELECT * FROM v$object_usage;

Disable monitoring of an index:

ALTER INDEX index_name NOMONITORING USAGE;

To enable monitoring on all indexes:

SET heading off
SET echo off
SET pages 10000
SPOOL start_monitoring.sql
SELECT 'alter index ' || owner || '.' || index_name || ' monitoring usage;'
FROM dba_indexes
WHERE owner NOT IN ('SYS', 'SYSTEM');
SPOOL off
@start_monitoring.sql

To stop monitoring on all indexes:

SET heading off
SET echo off
SET pages 10000
SPOOL stop_monitoring.sql
SELECT 'alter index ' || owner || '.' || index_name || ' nomonitoring usage;'
FROM dba_indexes
WHERE owner NOT IN ('SYS', 'SYSTEM');
SPOOL off
@stop_monitoring.sql

Query to find unused indexes:

SELECT * FROM dba_object_usage WHERE used = 'NO';

Imp:If you analyze an index, it will be marked as “used”.