Oracle materialized view - rebuiliding que

Question

I have a series of materialized views in Oracle. The content of next mview depends on content in previous one. So I'd like to create a schedule for these mviews where the next rebuild process starts when previous mview finishes its own rebuilding process. I cannot predict how long rebuilding process will take so I'd like to put them in kinda rebuilding que.

How to do that?

Answer 1

Use Oracle Scheduler to build a job chain to refresh them in the order desired. You can even have steps in the chain execute in parallel if there are dependencies that allow it, then have later steps wait for all of the parallel steps to complete before proceeding.

1. Create stored procedures to refresh each materialized view.
2. Create Scheduler Programs for each stored procedure.
3. Create a Scheduler Job Chain with Steps for each Scheduler program. Include Rules to control the order of Step execution.
4. Create a Scheduler Job to start the Chain execution at the desired time/interval.

See here for documentation and examples:

• Scheduling Jobs with Oracle Scheduler
• How To Do It In Parallel
• Scheduler in Oracle Database 10g Onward