Snapshot Creation - SQL & PL/SQL

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.

Related

Snapshot Schedule

Hey,
I have created several snapshots with 'Start With' set to sysdate and next set to sysdate + 1 and I was just wondering what would happen if for some reason the materialized wasn't updated on a particular day, would the schedule resume with the update the following day or would the schedule need to be set again?
I am asking this because it seems some of my views are not being updated on the schedule I initially created them with and I am trying to find out why.
Thanks 
get the infos about your snapshots via:
select l * from dba_snapshots
You can check the column LAST_REFRESH and ERROR 
Hi,
i already had this case
It seems that you have not to set again the schedule, he resumed himself to the next schedule.
Cheers

Snapshots

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

Materialized View not Getting Refreshed at Proper Intervals

Hello,
My Oracle version is 10g R2
We face this Problem quite often ... I mean sometimes my MV's does not get refreshed at proper Intervals
I have set the Refresh Interval at every 2 hrs to be on the safer side...as it takes 40-45 mins to refresh.
So here i am not getting any intimation that my MV is not refreshed since last 12 hrs ..inspite of 2 hrs refresh interval ... Hence the Bussiness users who are using the data from that MV ... tells us that they are still getting Yesterdays data ...
So is there any Way to find out or any intimation we can get that your MV is Not refreshed since so many hrs...
& one more doubt ... after knowing tht MV was not Refreshed i Refreshed it Explicitly ... as it was 'ON DEMAND' with this Command..
------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXEC DBMS_MVIEW.REFRESH('MV_RPT_MIS','C',NULL,NULL,NULL,NULL,NULL,128,TRUE);
------------------------------------------------------------------------------------------------------------------------------------------------------------------
But after this the Syntax of my MV changed .. as in the 'START With ' clause was not there ... what is the reason for this ..?? 
Please someone help or Answer..?? 
i am not getting any intimation that my MV is not refreshed When the Refresh is submitted as a schedule, it is executed as a Job. If a execution of the Job fails, Oracle writes to the alert.log and trace files. You have to check the alert.log and trace files.
You can also query USER_JOBS
is there any Way to find out or any intimation we can get that your MV is Not refreshed since so many hrsQuery USER_MVIEWS for LAST_REFRESH_TYPE and LAST_REFRESH_DATE. You could setup this query as a monitoring job.
Syntax of my MV changed .. as in the 'START With ' clause was not there What do you mean by this ? "START WITH" only applies to the first Build. Not to subsequent refresh's.
Hemant K Chitale 
Hello,
Well thanks Hemant for answerig my Previous Question...
But my second question was ..?? that when i Created a MV...My Syntax was ..??
--------------------------------------------------------------------------------------------------------------------
CREATE MATERIALIZED VIEW MV_RPT_MIS
REFRESH COMPLETE ON DEMAND
START WITH SYSDATE NEXT SYSDATE + 60/(24*60) -- hence this line is used when we start for first time & its Interval ...
AS
SELECT * FROM vw_rpt_mis
--------------------------------------------------------------------------------------------------------------------
Now as i said ... that some Business Users told us they they are simply getting the old Data ... that time i checked my MV ... & i saw that it was Last Refreshed some 20 hrs Back...& hence was not updated with the latest Values...
So i Refreshed it Explicitly by command --- " EXEC DBMS_MVIEW.REFRESH('MV_RPT_MIS','C',NULL,NULL,NULL,NULL,NULL,128,TRUE); "
And after running this when i saw the Syntax of my View ... there was no "START with " Clause....
it was only like ...
--------------------------------------------------------------------------------------------------------------------
CREATE MATERIALIZED VIEW MV_RPT_MIS
REFRESH COMPLETE ON DEMAND
AS
SELECT * FROM vw_rpt_mis
-------------------------------------------------------------------------------------------------------------------- 
The START WITH is used only at the first Build. It is irrelevant after that. It is not really part of the definition.
Hemant K Chitale

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.

How does materialized view work when refresh the view vs create the view?

I am using a materialized view, and I cant set it to fast refresh because some of the tables are from remote database which does not have materialized view log.
When I create the materialized view, it took like 20 30 seconds. however when I was trying to refresh it. It took more than 2 3 hours. and total number of records are only around 460,000.
I understand that when the view refreshes, it would take extra step like purge the table however I am not sure why it made so much difference in execution time perspective...
Does the dbms_mview.refresh('MY_MATVIEW', 'C', atomic_refresh=>false) do the same exact insert as when I created the view?
Could anyone please let me know how would this happen? Thanks,
Code looks like as following
create materialized view MY_MV1
refresh force on demand
start with to_date('20-02-2013 22:00:00', 'dd-mm-yyyy hh24:mi:ss') next trunc(sysdate)+1+22/24
as
( SELECT Nvl(Cr.Sol_Chng_Num, ' ') AS Change_Request_Nbr,
       Nvl(Sr.Sr_Num, ' ') AS Service_Request_Nbr,
       Nvl(Sr.w_Org_Id, 0) AS Org_Id,
       Fcr.rowid,
       Cr.rowid,
       Bsr.rowid,
       Sr.rowid,
       SYSDATE
  FROM Dwadmin.f_S_Change#DateWarehouse.World Fcr
INNER JOIN Dwadmin.d_S_Change#DateWarehouse.World Cr
    ON Fcr.w_Sol_Chng_Id = Cr.w_Sol_Chng_Id
INNER JOIN Dwadmin.b_S_Change_Obl#DateWarehouse.World Bsr
    ON Fcr.w_Sol_Chng_Id = Bsr.w_Sol_Chng_Id
INNER JOIN Dwadmin.d_S_Rec#DateWarehouse.World Sr
    ON Sr.w_Srv_Rec_Id = Bsr.w_Srv_Rec_Id
WHERE Sr.Sr_Num !='NS'
);I have tried to use dbms_mview.refresh('MY_MATVIEW', 'C', atomic_refresh=>false) but it still took 141 mins to run... vs 159 mins without atomic_refresh=>false
Edited by: 986006 on Feb 20, 2013 2:15 PM 
Welcome to the forum!
Whenever you post provide your 4 digit Oracle version (result of SELECT * FROM V$VERSION)
>
I am using a materialized view, and I cant set it to fast refresh because some of the tables are from remote database which does not have materialized view log.
When I create the materialized view, it took like 20 30 seconds. however when I was trying to refresh it. It took more than 2 3 hours. and total number of records are only around 460,000.
I understand that when the view refreshes, it would take extra step like purge the table however I am not sure why it made so much difference in execution time perspective...
Does the dbms_mview.refresh('MY_MATVIEW', 'C', atomic_refresh=>false) do the same exact insert as when I created the view?
>
What Oracle version are you using? The 'atomic_refresh' parameter default was changed to TRUE starting with 10g.
See the PL/SQL Packages and Types doc
>
atomic_refresh
If this parameter is set to true, then the list of materialized views is refreshed in a single transaction. All of the refreshed materialized views are updated to a single point in time. If the refresh fails for any of the materialized views, none of the materialized views are updated.
If this parameter is set to false, then each of the materialized views is refreshed in a separate transaction.
>
What that means is that if the value is TRUE Oracle uses a DELETE to remove the existing rows. If the value is FALSE then Oracle uses TRUNCATE.
If you really are using the FALSE setting you should not be seeing that much of a time difference between creating the MV and a complete refresh so something else must be going on. 
Sorry, I forgot to mention... I am currently using 10.2.0.3.0....
I was thinking the same, probably the remote box had performance issue or something... however I did a test couple times throughout the day...
Whenever I ran the drop and create materialized view, it took no more than 30 seconds. And I ran a command to refresh right after, it took no lesser than 2 hours...
That is why I was trying to understand is there any different in the insert between the refresh command and create view... 
>
That is why I was trying to understand is there any different in the insert between the refresh command and create view...
>
For a complete refresh the only difference would be the ATOMIC setting. Acts like Oracle is doing a delete instead of a truncate but you said it is NOT a partitioned MV.
Check V$SESSION_LONGOPS during the refresh to see what operation Oracle is performing.
http://docs.oracle.com/cd/B28359_01/server.111/b28320/dynviews_3021.htm#REFRN30227
>
V$SESSION_LONGOPS displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution, and more operations are added for every Oracle release.
>
You also may want to do a 10046 trace to see if it captures the detail of what is going on. 
I do not have the access to the database box... Will I still be able to view the log file? 
Well you need to be able to access the system views if you want to troubleshoot problems like this. You don't need physical access or connectivity to the server itself, just to Oracle.
You would need to have the DBA access the trace file for you though.

Categories

Resources