0

On one of my production Oracle database 11g Standard Edition I have a lot (a very lot of) indexes. Some of them (majority) are created by the application installer, others are created by me when I identify a request who need some dba work. I'm pretty sure that some of them aren't used at all and I want to identify them.

For that task, I alter all my table & index setting MONITORING status.

After a while (several weeks) I watched about used status :

SELECT index_name, table_name, monitoring, used,
       start_monitoring, end_monitoring
  FROM v$object_usage
 ORDER BY index_name;

v$object_usage query result

I notice that a lot of indexes have now a NOMONITORING status ...

I search to understand what happened and why about this status.

Except the regular ALTER INDEX index_name NOMONITORING USAGE query, what is likely to change this? Does anyone know a process (a standard Oracle's in-dictionary process) that remove the MONITORING status?

1

One possible reason:

Bug 16311211 : REBUILDING INDEX ALTER SETTING THE MONITORING TO OFF

The bug is said to be fixed on version 12.2 and there are one-off patches for 11.2.0.3 and 11.2.0.4 as well.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.