[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


Materialized view refresh every day at 6am.

Posted on 2014-02-14
Medium Priority
Last Modified: 2014-02-17
Can someone help me out?

Trying to figure out a couple of things.

First, I have a materialized view, i need to refresh everyday at 6am.

create materialized view sometable as
select * from sometable

Open in new window

Complete refresh, the remote database is non-oracle.


Can i have multiple materialized views refresh at the same time at 6am? or should i do them one after another. In that case, how can I start the materialized view refresh to execute one after another?

Question by:FutureDBA-
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39860753

Check out the NEXT clause on the materialized view.

Try something like:
create materialized view sometable as
build immediate
refresh fast
start with trunc(sysdate)+6/24
next trunc(sysdate)+6/24
select * from sometable

Open in new window

I doubt they would all run at the exact same time.  It would also put a strain on the system.  I would try to stretch them out.
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39860758
Forgot this:
>>how can I start the materialized view refresh to execute one after another?

Don't set the NEXT value and create a stored procedure to call REFRESH then dbms_scheduler to call the procedure.

Author Comment

ID: 39860768
So Can i create this procedure

CREATE PROCEDURE sometable_dailyrefresh as 
DBMS_SNAPSHOT.REFRESH( '"CDC"."sometable"','C');
DBMS_SNAPSHOT.REFRESH( '"CDC"."othertable"','C');
DBMS_SNAPSHOT.REFRESH( '"CDC"."andanothertable"','C');
DBMS_SNAPSHOT.REFRESH( '"CDC"."lasttable"','C'); 

Open in new window

and have the scheduler run this everyday at 6am

this will refresh them one after another ?
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 total points
ID: 39860773
>>this will refresh them one after another ?

Yes.  Stored procedure code is executed sequentially.

I can't speak to the syntax of the DBMS_SNAPSHOT.REFRESH calls you posted.  I would have to defer to the online docs but they look correct assuming you forced case sensitivity on the views by using double quotes.
LVL 74

Expert Comment

ID: 39860793
or put them into a refresh group and refresh the group rather than refreshing each one individually.

That's what refresh groups are for.

Author Comment

ID: 39863794

can you elaborate further on refresh views or point me in the right direction to documentation on what you are speaking of.

I did a search for oracle refresh group and it seems that search is too broad, so not sure where to start.
LVL 74

Accepted Solution

sdstuber earned 1000 total points
ID: 39863858

Simple example.  If you have 3 materialized views...


you can put them all in one refresh group with a daily refresh at 6am
with the  MAKE procedure

        name        => 'MY_REFRESH_GROUP',
        list        => 'MY_MV_1, MY_MV_2, MY_MV3',
        next_date   => TRUNC(SYSDATE) + 1 + 6 / 24,
        interval    => 'TRUNC(SYSDATE)+1+6/24'

Or to refresh it manually...


Note, if you already have jobs to refresh them individually, creating the refresh group does not remove those jobs, so your MVs could refresh multiple times.

It's best to pick one or the other scheduling method and then drop the jobs of the other method.

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question