0

I have created the materialized views using DB link. I am facing an strange problem with Oracle 11g2 materialized view. When invoke first time sometimes it does not reflect the changes(insert and update) as per source table changes. When invoke the same MV second time it works fine. The number of rows may be more than 50,000.

Please find MV creation script.

CREATE MATERIALIZED VIEW "EMRSODEVAPPUSER"."MV_BM_PNS" ("BM_UNIT", "DATETIME_FROM", "DATETIME_TO", "LEVEL_FROM", "LEVEL_TO")
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  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 "EMRSODEVTBS"
  BUILD IMMEDIATE
  USING INDEX
  REFRESH FORCE ON DEMAND NEXT null
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
  AS (select * from (select * from BM_PNS@TRSP04));
   COMMENT ON MATERIALIZED VIEW "EMRSODEVAPPUSER"."MV_BM_PNS"  IS 'snapshot table for snapshot EMRSODEVAPPUSER.MV_BM_PNS';

This is called by DBMS refresh group:

Create the group :

DBMS_REFRESH.MAKE(
name=>'MV_REFRESH_GROUP', 
LIST=>'MV_BM_PNS',next_date => SYSDATE, INTERVAL => 'null');
END;
/   

Execute the group :
execute dbms_refresh.refresh('MV_REFRESH_GROUP');

Thanks in advance !

sandeep pandit
  • 111
  • 1
  • 1
  • 7
  • Please have a look at [ask]; among the others, consider "Pretend you're talking to a busy colleague", so things like "answer on immediate basis" are not welcome here. Why `select * from (select * from BM_PNS@TRSP04)`? – Aleksej Sep 20 '16 at 13:27
  • Edited and removed that "answer on immediate basis". Thanks for reminding me. select * from (select * from BM_PNS@TRSP04) could be an issue ? – sandeep pandit Sep 20 '16 at 13:49

0 Answers0