Technology Network Community
Oracle Database
Fusion Middleware
Development Tools
Server & Storage Systems
Enterprise Management
Berkeley DB Family
Cloud Computing
Big Data
Business Intelligence
Migration and Modernization
E-Business Suite
PeopleSoft Enterprise
JD Edwards World
JD Edwards EnterpriseOne
User Productivity Kit Pro (UPK) and Tutor
Governance, Risk & Compliance (GRC)
Master Data Management (MDM)
Oracle CRM On Demand
On Demand: SaaS and Managed Applications
AutoVue Enterprise Visualization
Agile PLM
Endeca Experience Management
Fusion Applications
Archived Forums



Enterprise Manager

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.

Related Links

Which other Useful Reports(Scheduled) Can i fetch from OEM grid Control?
media creation?
Grid Control not able to Discover WebSphere 6.1.x
OMS won't start
Installing GC R5 on SLES 10
Can multistep job be set up to run job B after job A completes?
EM doesn't connect with database
Management Agent Install RHEL 64 for
connecting oms to agents and DB behind FW
scheduled GridControl jobs get not executed
Grid Control Report: Host having Avg CPU and Memory Utilization > 80%?
agent unreachable
emctl start dbconsole -> fail 10g (
Changing the font color in grid report.
strange grid control behaviour
500 internal server error setDbUserPassword