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

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.

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

Materialized view Refresh

I am trying to create a materialized view which refreshes itself everyday at 10pm.
REFRESH FORCE ON DEMAND START WITH sysdate NEXT trunc((sysdate+1)+10/24)
This does the job for 10am so will
REFRESH FORCE ON DEMAND START WITH sysdate NEXT trunc((sysdate+1)+22/24)
do the trick...
Thanks 
Is this a problem or solution???
It seems that you are answering thread with new thread!!! 
Question
Will REFRESH FORCE ON DEMAND START WITH sysdate NEXT trunc((sysdate+1)+22/24) do the trick???? 
I guess it will work!
Try to run it in dev If no syntax error than 100% it's working! :) 
Thanks.. 
You are welcome! 
Hi,
I think that won't work as the statement trunc((sysdate+1)+10/24) doesn't contain any time information. Instead you can try this:
next to_date(to_char(sysdate+1,'dd/mm/yyyy')||''||'10','dd/mm/yyyy hh24')
hope this will work. 
You're right, trunc((sysdate+1)+10/24) doesn't contain time information, but if you put the parens in the right places it does :-)
SQL> SELECT TRUNC(sysdate+1)+(10/24)
  2  FROM dual;
TRUNC(SYSDATE+1)+(10
--------------------
09-may-2008 10:00:00John

Materialized view

Hi all,
How to check refresh frequency of a Materialized view?
I checked in user_mview table but it only tells about last refresh time and date.
plz help
thanx in advance. 
How are you refreshing it ? From dba_jobs ? From dba_scheduler_jobs ? From cron job ? 
http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/statviews_1148.htm#i1582388
You can examine or track history from here.
Edited by: drop.any on Feb 1, 2010 4:29 AM 
I just created by create materialized view with refresh rate per hour.
but now i want to check that frequency....then whats the query? 
ALL_MVIEW_REFRESH_TIMES & DBA_MVIEW_REFRESH_TIME are also giving last refresh time of MV but not giving the rate. 
If you use the default refresh method, then they are probably being refreshed from dba_jobs.
select job, what, last_date, this_date, next_date from dba_jobs; 
thanx buddy, I got my answer. thanx a lot

dbms_refresh.refresh( MYJOB)

Hi,
on 10g R2, materialized view refreshes do not run since several days. In alert log there nothing about job failure and in dba_jobs they have BROKEN=N. It was a problem of DBLINK. I recreated the DBLINK.
What should I do to :
make them re run again ? Will the jobs re run automatically ?
Thank you. 
Something similar issue has been discussed at below link too :
http://www.orafaq.com/forum/t/101154/2/
Senior Member is saying :
1. ALTER SESSION SET QUERY_REWRITE_INTEGRITY=STALE_TOLERATED;
2. What does dbms_mview.explain_mview show?
Can you please show us your MVs DDL and How you are using dbms_refresh.... complete command.
Regards
Girish Sharma 
Thank you.
The only question is to change next run time.
Can an
ALTER MATERIALIZED VIEW my view
works for changing next date ?
If yes how can I use it to say
ALTER MATERIALIZED VIEW myview NEXT TIME 24 January
Thanks. 
I think you will not happy; if i says to read the docs, but all these are very clear there :
http://docs.oracle.com/cd/B13789_01/server.101/b10759/statements_2001.htm#i2226546
START WITH Clause
Specify START WITH date to indicate a date for the first automatic refresh time.
NEXT Clause
Specify NEXT to indicate a date expression for calculating the interval between automatic refreshes.
Both the START WITH and NEXT values must evaluate to a time in the future. If you omit the START WITH value, Oracle Database determines the first automatic refresh time by evaluating the NEXT expression with respect to the creation time of the materialized view. If you specify a START WITH value but omit the NEXT value, Oracle Database refreshes the materialized view only once. If you omit both the START WITH and NEXT values, or if you omit the alter_mv_refresh entirely, Oracle Database does not automatically refresh the materialized view.
Now look on your requirement and I am sure, you can simply alter the START and NEXT clause for your MV's refresh time/interval.
Regards
Girish Sharma 
I think you will not happy; if i says to read the docs, but all these are very clear there :
http://docs.oracle.com/cd/B13789_01/server.101/b10759/statements_2001.htm#i2226546
START WITH Clause
Specify START WITH date to indicate a date for the first automatic refresh time.
NEXT Clause
Specify NEXT to indicate a date expression for calculating the interval between automatic refreshes.
Both the START WITH and NEXT values must evaluate to a time in the future. If you omit the START WITH value, Oracle Database determines the first automatic refresh time by evaluating the NEXT expression with respect to the creation time of the materialized view. If you specify a START WITH value but omit the NEXT value, Oracle Database refreshes the materialized view only once. If you omit both the START WITH and NEXT values, or if you omit the alter_mv_refresh entirely, Oracle Database does not automatically refresh the materialized view.
Now look on your requirement and I am sure, you can simply alter the START and NEXT clause for your MV's refresh time/interval.
Regards
Girish Sharma 
Thanks Sharma,
should OracleJobSchedulerMYDB Windows service be running to have refresh job re planified ?
Other question just alter_mv_refresh in creation script would be enough ?
This is my creating script :
SET DEFINE OFF;
DROP MATERIALIZED VIEW PEOPLESOFT.TBL;
CREATE MATERIALIZED VIEW PEOPLESOFT.TBL
TABLESPACE PEOPLESOFT_DATA01
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
USING INDEX
            TABLESPACE PEOPLESOFT_INDEX01
REFRESH FAST
START WITH TO_DATE('25-janv.-2012 07:06:44','dd-mon-yyyy hh24:mi:ss')
NEXT SYSDATE+1/24 
WITH ROWID
AS
SELECT column1 from TBL"#ADB;
COMMENT ON MATERIALIZED VIEW PEOPLESOFT.TBL IS 'snapshot table for snapshot PEOPLESOFT.TBL';Where should I add alter_mv_refresh ?
Thanks and regards. 
user522961 wrote:
Thank you.
The only question is to change next run time.
Can an
ALTER MATERIALIZED VIEW my view
works for changing next date ?
If yes how can I use it to say
ALTER MATERIALIZED VIEW myview NEXT TIME 24 January
Thanks.ALTER MATERIALIZED VIEW PEOPLESOFT.TBL
START WITH TO_DATE('24-JAN-2012 07:06:44', 'DD-MON-YYYY hh24:mi:ss');
START WITH and NEXT are optional clauses.
More Examples :
http://docs.oracle.com/cd/B13789_01/server.101/b10759/statements_2001.htm#i2226546
Regards
Girish Sharma
Edited by: Girish Sharma on Jan 25, 2012 7:25 PM 
Thank you.
We did this :
ALTER MATERIALIZED VIEW PEOPLESOFT.TBL
START WITH TO_DATE('25-janv.-2012 07:01:44','dd-mon-yyyy hh24:mi:ss')
NEXT SYSDATE+1/2But the refresh job did not run
select job, LAST_DATE, to_char(NEXT_DATE,'DD/MM/YYYY hh24:mm:ss') from dba_jobs where schema_user like '%PEOPLESOFT%'
       JOB LAST_DAT TO_CHAR(NEXT_DATE,'
---------- --------  -------------------
      411         25/01/2012 07:01:44
1 row selected. 
You have given wrong START WITH value.
Because if i says your value something like this :
SQL> select TO_DATE('25-janv.-2012 07:01:44','dd-mon-yyyy hh24:mi:ss') from dual;
select TO_DATE('25-janv.-2012 07:01:44','dd-mon-yyyy hh24:mi:ss') from dual
               *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0And if i says :
SQL> select TO_DATE('25-jan-2012 07:01:44','dd-mon-yyyy hh24:mi:ss') from dual;
TO_DATE('
---------
25-JAN-12START WITH .... time has been gone and START WITH clause says first automatic refresh time. It means, it will refresh on next SYSDATE+1/2 i.e. 26.JAN 2012 at 07:01:44 i.e. after 12 hrs (sysdate+1/2) from 25-janv.-2012 07:01:44.
So, just give correct START WITH values and wait for next interval pass (if START WITH time has been passed).
Regards
Girish Sharma

Categories

Resources