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:
- Disable to the scheduler job
- Turn the MV into table (DROP MATERIALIZED VIEW .. PRESERVE TABLE; )
Then we can update the table data manually for our maintenance work.
After the maintenance work, I would:
- Turn the table back to MV
- Re-enable the scheduler job to refresh the data
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.
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.
CodePudding user response：
What is the reason to update some data inside the mview? Maybe you need a table, not a mview. Just refresh the materialized view to update the data, or change the related SQL to reflect the dataset you want. Other way it would be as inconsistent state.
CodePudding user response：
- Turn the table back to MV
If you understand that after that step, the MV is refreshed with the life data and your changes to the underlaying table are lost, it's OK.
Another way to do the job is to access the MV through a VIEW, and changes the definition of the VIEW for the maintenance work (conceptually "select from MV where not exists (select FROM PATCH) UNION ALL select from PATCH"), and putting it back to "select FROM MV" when done). (And note that truncate PATCH will have the same effect, and you don't have to change the VIEW...)