How to find unused indexes To determine if your indexes are being used or you can drop them use index monitoring (this feature is available from Oracle 9 onwards).
1. To start index monitoring:
SQL> alter index SAMPLE_INDEX monitoring usage;
If this statement fails with
ORA-00054: resource busy and acquire with NOWAIT specified
The index is already being used, so ...
How to remove a parameter from spfile This feature is available in Oracle 9 and Oracle 10. To remove a parameter from spfile and reset it to default value run the following command:
alter system reset "_db_block_lru_latches" scope=spfile sid='*';
