Snapshots - SQL & PL/SQL

I want to create a snapshot that will start the next day at 4:30
am and refresh every day at 4:30 am.
I would like to know if my snapshot creation is OK:
create snapshot TEST_SNP
refresh complete
start with trunc(sysdate+1)+(4.5/24)
next round(sysdate)+4.5/24
as select * from TEST_VIEW
Thanks in Advance
Thiru

Related

Materialized View

i have a materliazed view which start to refresh complete at 4:00 AM night , how can i know that till how long it refreshed to be completed? 
Refer:-http://dbaforums.org/oracle/index.php?showtopic=20951

how to change refresh time.

Hi,
I created materialized view refresh like below.
refresh start with trunc(sysdate)+4/24                                                                  
next trunc(sysdate+1)+4/24   Now i want to schedule for next refresh for 5.60CSTcan some one advice. 
what is 5.60CST??
CST - I know it is the time zone.
What does 5.60 implies ...is it after every 5.6 hours.
Ravi Kumar 
CREATE MATERIALIZED VIEW M1
NOCACHE
NOLOGGING
COMPRESS
NOPARALLEL
BUILD DEFERRED
REFRESH FAST ON DEMAND
START WITH TO_DATE('02-Dec-2009 12:00:00','dd-mon-yyyy hh24:mi:ss') /*Set the start date here*/
NEXT trunc(sysdate) + 1 ---/* Change the refersh time here */
AS
/*UR QUERY*/ 
sorry i was mistake .it was 5.30EST for one time. with dbms scheduler.

Snapshot Creation

I want to create a snapshot that will start next day at 4:30 am
and refressh everyday at 4:30 am. Is this statement OK?
create snapshot TEST_SNP
refresh complete
start with trunc(sysdate+1)+(4.5/24)
next round(sysdate)+4.5/24
as select * from TEST_VIEW
The Last_refresh columns in User_Snapshots always shows 01-Jan-
1950(bug)and I am not able to check this snapshot properly.
Thanks
Would't you have the same start with and next clause? Not sure
why you are using hte round function. yes, the LAST_REFRESH_TIME
is a bug in ORacle 8i. There are "work-arounds" to find otu that
info though.

Materialized view update

My requirement is update a Materialized View every hour, at 08:00, 09:00, 10:00, ..., all day.
The create MV is:
create materialized view my_mv
refresh start with trunc(sysdate)+(15/24)
        next sysdate+1/24
as
select ....In fact, the view is been updated in a interval of an hour plus a few seconds. Three or four days later, the seconds are accumulated to minutes, and the MV is been updated fifteen (or more) minutes later as my intention.
I think that it is consequence of the running time of the update process.
I'm using Oracle 9.2.0.4
Any sugestion? 
next TRUNC(sysdate+1/24) 
next TRUNC(sysdate+1/24,'hh')Best regards
Maxim 
I changed on Friday and verified today (all weekend working). It´s ok, no delays due to response time.
Thank you,
Miguel

How to create materialized view to refresh every X hours?

Hi, at the time of a materialized view creation, I want to specify that it should be refreshed every 6 hours starting the next day at 12am, 6am, 12pm, 6pm and so on. I'm a little confused on how to do this using the NEXT clause. The documentation says for the NEXT clause to "Specify a datetime expression for calculating the interval between automatic refreshes." I'm not sure if the refresh interval is calculated by taking the difference between the NEXT and START WITH times? Would the following syntax be correct for what I want to do?
CREATE MATERIALIZED VIEW test_mv REFRESH COMPLETE START WITH TRUNC(SYSDATE+1) NEXT TRUNC(SYSDATE+1)+6/24 as ....
I am not sure if this will only refresh once a day at 6am. I guess I can wait to see for sure, but hopefully, I can get an answer here faster. If the above is not correct, can someone offer a suggestion? Thanks. 
The different between NEXT and START WITH is that "START WITH" is only used the first time and "NEXT" is for the rest of times.
In example, you want that the first time of your materialized view refresh will be tomorrow at 02:00 am and the next times will be every 1 hour, so your statement will be something like
CREATE MATERIALIZED VIEW xxxxx
REFRESH COMPLETE
START WITH TO_DATE('12-SEP-2012 02:00:00','dd-mon-yyyy hh24:mi:ss')
NEXT (SYSDATE +1/24)
AS
SELECT * FROM user_tables;
HTH 
Thanks Fran. So if I understood correctly, the following should work for me to start refreshing the view at 12am tomorrow and then every 6 hours afterwards (so that refreshes take place everyday at 12am, 6am, 12pm, 6pm)?
CREATE MATERIALIZED VIEW test_mv REFRESH COMPLETE START WITH TRUNC(SYSDATE+1) NEXT sysdate+6/24 as .... 
yes, your first time will be tomorrow at 00:00:00, and the second time (and others) each 6 hours, 06:00:00, 12:00:00, 18:00:00, .... 
Thanks a lot. I'll watch the refreshes to confirm, but I think you're right. I wish the documentation was clearer with examples on how the NEXT clauses works. Hope this posting may help others with a similar question.

Categories

Resources