How to find indexes that are not in use on Oracle

Finding unused indexes on Oracle

Many times we are in need to find out or provide the list of Indexes that are used/not  used so that we can come to a decision whether to keep them or to drop them

Here is a quick way how you can enable monitoring for Index usages and query dba_object_usage or v$object_usage view later to see how frequently the indexes are being used. Starting from 12c v$object_usage view is depricated and therefore I recommend to use dba_object_usage

How to Enable Monitoring on Index Usages

alter index   monitoring usage;

SQL> alter index idx1  monitoring usage; 

How to Find the Index Usages

select index_name, table_name, used from v$object_usage;

or

select index_name, table_name, used from dba_object_usage;

How to disable Monitoring on Index Usages

alter index nomonitoring usage;

SQL> alter index  idx1 nomonitoring usage;

How to generate script to enable Index monitoring for all Indexes owned by an User

SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

SPOOL enable_index_usages_monitoring.sql
SELECT 'ALTER INDEX "' || i.owner || '"."' || i.index_name || '" MONITORING USAGE;'
FROM   dba_indexes i
WHERE  owner      = UPPER('&OWNER')
SPOOL OFF

SET PAGESIZE 18
SET FEEDBACK ON

To enable Monitoring you can simply execute the scipt as shown below

SQL>@enable_index_usages_monitoring.sql