Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to identify the unused indexes

MAWNov 23 2010 — edited Nov 23 2010

Hi All,

I want to know to find the unused indexes in the database. I want to identify the unused indexes to drop them and free up some space for the other database objects. Is there any view or data dictionary where I can query and find the currently unused indexes in the database?

Oracle database version: 11.1.0.7

I know one method below but this is not efficient as I have to turn on monitoring for all the indexes and wait for days to check whether they are really being used.

1) start index monitoring
2) execute SELECT statements
3) Stop index monitoring
4) Query v$object_usage view.

Regards,

Locked Post

New comments cannot be posted to this locked post.

Locked on Dec 21 2010

Added on Nov 23 2010

#general-database-discussions

3 comments

6,888 views