Can not Drop Materialized View on 12c and above?

Hello Everyone,

I couldn’t write for a long time because of a crazy workload. During this heavy hardworking, I faced with a problem on one of my customers. They have a on commit refresh Materialized View (MV) and started to complain about they couldn’t insert into main table of MV. When I checked, I saw some error (which I don’t remember now) about refresh. Since it was an emergency, I run a complete refresh on MV and it was all ok but insert statement continued to fail on main table. So I decided to drop MV and I just realized that something else is also wrong because after 5 minutes, drop statement failed and even my session has lost. I tracked the drop comment and I saw that Oracle was trying to delete some tables:

sys.mvref$_stmt_stats
sys.mvref$_change_stats
sys.mvref$_stats
sys.mvref$_run_stats

after a quick search, I learned that those are new tables which came with 12c and above. Oracle stores some statistical information on them by default but they have no indexes and when you tried to drop an MV oracle try to delete those tables for that MV but because lack of indexes (probably) it gets an internal timeout.

So I simply delete (actually truncate) those tables and re-run drop MV command it worked! MV has been dropped in seconds. you can disable (or at least minimize) the logging level for MVs with:

I hope this helps to whoever has the same problem.

bye…