We have a materialized view (MV) on Oracle 19c. Its data is updated/refreshed by scheduler job every day.
Because of some maintenance work that may last for more than 6 months, I would like to update some data inside it manually. However, manual update is forbidden on MV.
It may sound stupid but I am planning to:
Then we can update the table data manually for our maintenance work.
After the maintenance work, I would:
So the question is... how do I turn the table back to MV SAFELY in my case? It is easy to turn MV into table but I have never heard anyone doing it the other way round.
By safely, I mean that the reverted MV is back to before without lost of behaviours/properties.
If I turn the MV to table and then back to MV, would the index still work for both the table and the MV without affected? Similarly, if we already have a synonym for the MV, would this synonym still work after converting to table and back to MV again? Do I need re-grant any user privileges to the table and later for the MV again?
Note: I am aware that after turning the table back to MV, the data get refreshed and our manual data would be lost. That is acceptable for us because we just want the manual data to stay during the maintenance period.
If there are other suggestions/alternatives, I am happy to hear.
Combining synonym, patch table and views might be a good solution for temporary situation as suggested. And then.. You can safely recreate materialized view with
CREATE MATERIALIZED VIEW testmv ON PREBUILT TABLE ...
Indexes can be used till refresh. You don't need to re-grant user privileges Synonyms that crated previously for MV still works after creating MV from prebuilt table.