Recently I've receive some alerts of unusable materialized view indexes and it happens when a materialized view is refreshed. The materialized view is refreshed with this command:

exec dbms_mview.refresh('MY_MV',atomic_refresh=>false);

atomic_refresh=>false always make the index unusable and then rebuild it? or could be other reason? I guess it make sense since atomic_refresh=>false truncate the table and probably rebuild the index is faster?

1 Answer 1


Do the indexes become usable again after refresh? If yes, you have probably explained the reason.

Actually, during non-atomic refresh, even the MView is not unusable. Who care about the indexes in that period?

  • Thanks, I was not sure about how to explain why the index unusable alert open every day, but make sense.
    – Astora
    Commented Oct 21, 2022 at 18:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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