It would always be good to drop your unused indexes means which are not selected although they may be suffering from too many DML's, so its better to drop them as they are not getting used by select queries and its not good performance that you use them only for DML's. Let us see how to monitor the indexes and get a report :-
To alter an index, your schema must contain the index or you must have the ALTER ANY INDEX system privilege. With the ALTER INDEX statement, you can:
# Rebuild or coalesce an existing index
# Deallocate unused space or allocate a new extent
# Specify parallel execution (or not) and alter the degree of parallelism
# Alter storage parameters or physical attributes
# Specify LOGGING or NOLOGGING
# Enable or disable key compression
# Mark the index unusable
# Make the index invisible
# Rename the index
# Start or stop the monitoring of index usage
Below script will generate commands all you need for an entire schema to monitor the indexes :-
SELECT ' alter index '||index_name||' MONITORING USAGE; ' FROM user_indexes ;
Index monitoring is started and stopped using the ALTER INDEX syntax shown below.
ALTER INDEX my_index_i MONITORING USAGE;
ALTER INDEX my_index_i NOMONITORING USAGE;
Information about the index usage can be displayed using the V$OBJECT_USAGE view.
In between you will also see below error :-
ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
This means you cannot alter this index at the moment as there is already some changes going and you cannot get an exlcusive lock on it. Interestingly you should ignore this because this means the index is already being used/changed but selected, so you can do monitoring later for these indexes.
WHERE index_name = 'IDX1';
The V$OBJECT_USAGE view does not contain an OWNER column so you must to log on as the object owner to see the usage data.