How to create Materialized View and Automatic Refresh Scheduler in Oracle 11gR2

 Sumber Gambar : http://annasophiawatts.com/wp-content/uploads/2014/12/Oracle-11g-RAC-Essentials-Certification-An-Overview.png

Before I post this article, I was troubled by MV configuration to refresh it manually. My MV is too complicated to tell it here, so I'll create it more simple.

Create a materialized view first, here is my simple MV :

 CREATE MATERIALIZED VIEW "some_schema"."MV_TEST_BRO" ("some_field", "some_field", "some_field")  
 ORGANIZATION HEAP PCTFREE 10 PCTUSED 0 INITRANS 2 MAXTRANS 255 NOCOMPRESS NOLOGGING  
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  
 TABLESPACE "some_tablespace"   
 BUILD IMMEDIATE  
 USING INDEX   
 REFRESH COMPLETE ON DEMAND  
 USING DEFAULT LOCAL ROLLBACK SEGMENT  
 USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE  
 AS SELECT * FROM some_table;  

You can create it using REFRESH COMPLETE ON COMMIT or using FAST/FORCE mode. For detailed information please read this. After you update one of your table master you will get status NEED_COMPILE if you run this query :

 SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS ORDER BY MVIEW_NAME;