Categories

JAVA DATEBASE
Technology Network Community
Oracle Database
Fusion Middleware
Development Tools
Java
Desktop
Server & Storage Systems
Enterprise Management
Berkeley DB Family
Cloud Computing
Big Data
Business Intelligence
Architecture
Migration and Modernization
E-Business Suite
Siebel
PeopleSoft Enterprise
JD Edwards World
Industries
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
Primavera
ATG
Agile PLM
Endeca Experience Management
Fusion Applications
Archived Forums

 



Tags

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

upgrading java question?
Creating a notification from Oracle Enterprise Manager to Outlook
Upgrade from 12.1.0.1 to 12.1.0.2 Failed with MDS Creation
EM12c: Target SQL Server 32-bit shows "Status Pending"
OEM Cloud Control 12c R2 Solaris Support ?
Agents plateform is not Databases plateform
Where to find self service log
Do we need to apply patches on Middleware componets ,for EM 12c to monitor
Monitor java heep with OEM
Location to start nodemanager in OEM 12c Cloud control
Extracting Data from OEM
Using OEM to monitor Oracle Database Appliance ?
How to add host in OEM for which agent is already deployed
Oracle 11g OEM oracle suggested backups
EM12c: installation fails on the step "Start Oracle Management Service"
12c Cloud Control - Agent Unreachable