How to Find Unused Indexes

You can use Monitoring feature of Oracle to find which indexes are unused. You can run the following query to enable monitoring on an index:

ALTER INDEX index_name MONITORING USAGE;

After you enable indexing, you should wait for a while (for example 1-2 days if you think that this index should be used daily), and then you can query v$object_view to see if index is used:

You can run the following query to disable monitoring of an index:

ALTER INDEX index_name NOMONITORING USAGE;

Disabling monitoring an index, does not change the “used” value on v$object_usage; If you query that view, you should still use the index as used. Because the “used” property shows if index is used between start_monitoring and end_monitoring period.