Materialized View -- Last Refresh date
After i created my materialized view with refresh force, then I alter the refresh to next sysdate+12/24 ( ie. every 12 hours). However, from the EM control, the last refresh date only indicated the original created date. Why? How can I sure that the materialized view is refreshed accordingly. My Oracle is 10g R2 on AIX 5.3 L. This mv is created on a dev server, so that master tables of the mv have not benn updated since week ago. I used following syntax to change to automatically refresh alter materialized view my_mv refresh next sysdate+12/24; According to the doc of mv, the refresh force mean when the master tables has any new date been commited, the new date will be copied to the my_mv. Am i right? does this data renewal/update has anything to do the refresh of my_mv? i.e only with new data, the refresh date will be changed?
Depends on what you used. Did you use REFRESH FAST ON COMMIT or REFRESH FAST ON DEMAND ? Did you create a Materialised viw log on the base table? Could you post your create mv script.
I first created with ON DEMAND CREATE MATERIALIZED VIEW "BAANDB"."R1_MV" TABLESPACE "USERS" NOLOGGING STORAGE ( INITIAL 128K) USING INDEX TABLESPACE "BAANIDX" STORAGE ( INITIAL 256K) REFRESH FORCE ON DEMAND ENABLE QUERY REWRITE AS SELECT CM.ROWID c_rid, PC.ROWID p_rid, CM."T$CWOC", "T$EMNO", CM."T$NAMA", CM."T$EDTE", PC."T$PERI", PC."T$QUAN", PC."T$YEAR", PC."T$RGDT" From BAANDB.TTPPPC235201 PC RIGHT OUTER JOIN BAANDB.TTCCOM001201 CM Using (T$EMNO) then I used alter materialized view r1_mv refresh next sysdate+12/24; so should be noew refresh according to new rule. I ever tried with ON COMMIT replacing ON DEMAND, but the sys return ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view. So I found this fix note 4740187.8 in metalink to use "ALTER.. " to switch to "NEXT.. interval". Yes I created materialized view log on both master tables. Acccoding to Metalink notes: 279168.1 (both my master tables are not IOT) and 301627.1 my master tables are local, not remote.
I DISABLE QUERY REWRITE in the script and then REFRESH Automatic NEXT works.