RefrMaterialized View

Question

I'm trying to get my materialized view to automatically refresh daily at 8:00 AM in the morning. I have written the below code but am not getting desired output:

CREATE MATERIALIZED VIEW empl_M_V
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 0 INITRANS 2 MAXTRANS 255 NOCOMPRESS 
  NOLOGGING STORAGE(INITIAL 524288 NEXT 524288 MINEXTENTS 1 
                    MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 
                    FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "MITG_DATA"
  USING INDEX
  REFRESH START WITH TO_DATE('10-jul-2012 20:09:00', 'DD-Mon-YYYY HH24:MI:SS')
  NEXT ROUND(SYSDATE+1 )+ 8/24 + 30/1440
  AS
  SELECT * from support

Please help me out

score 1 · Answer 1 · answered Jul 13 '12 at 12:34

You are using ROUND function wich gives you the next day if you are after 12PM. So, because your time is, I suppose, 20:03, your MV will run not tomorrow, but a day after tomorrow.

Just to be sure what you've done, run:

select ROUND(SYSDATE+1 )+ 8/24 + 30/1440 from dual;

However, the sollution is to use trunc instead of round.

RefrMaterialized View

1 Answers1