Solved

Materialized view refresh every day at 6am.

Posted on 2014-02-14
7
4,363 Views
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.

Second.

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?

Thanks,
0
Comment
Question by:FutureDBA-
  • 3
  • 2
  • 2
7 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39860753
First:

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
as
select * from sometable

Open in new window


Second:
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.
0
 
LVL 76

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.
0
 

Author Comment

by:FutureDBA-
ID: 39860768
So Can i create this procedure

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

Open in new window


and have the scheduler run this everyday at 6am

this will refresh them one after another ?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 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.
0
 
LVL 73

Expert Comment

by:sdstuber
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.
0
 

Author Comment

by:FutureDBA-
ID: 39863794
sdstuber,

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.
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
ID: 39863858
http://docs.oracle.com/cd/E11882_01/server.112/e10707/rarrefreshpac.htm#REPMA018

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

MY_MV_1
MY_MV_2
MY_MV_3

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

BEGIN
    DBMS_REFRESH.make(
        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'
    );
END;
/


Or to refresh it manually...

BEGIN
    DBMS_REFRESH.refresh('MY_REFRESH_GROUP');
END;


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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now