Here is my materliazed view
CREATE MATERIALIZED VIEW HZ_PARTY_SITES_MV
REFRESH FAST ON DEMAND
START WITH TO_DATE('08-07-2011', 'DD-MM-YYYY') NEXT ROUND(SYSDATE)
AS
SELECT * from HZ_PARTY_SITES@PROD.PARAZELSUS.PK
i expect that this view should be refreshed at midnight at 13-jul-2011 00:00:00
On 11th July
------------
SQL>select
to_char(SYSDATE,'dd-mon-yyyy hh24:mi:ss') "current"
,to_char(ROUND(SYSDATE),'dd-mon-yyyy hh24:mi:ss') "future"
from dual
current future
-------------------- --------------------
11-jul-2011 13:57:02 12-jul-2011 00:00:00
But it is not refreshing when i see alert logfile i find the following error.
Tue Jul 12 00:02:47 2011
Errors in file /orahome/app/oracle/diag/rdbms/repsrv/repsrv/trace/repsrv_j002_26218.trc:
ORA-12012: error on auto execute of job 84
ORA-12005: may not schedule automatic refresh for times in the past
upon checking trace file
Trace file /orahome/app/oracle/diag/rdbms/repsrv/repsrv/trace/repsrv_j002_26218.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /orahome/app/oracle/product/11.1.0/db_1
System name: Linux
Node name: repsrv.parazelsus.pk
Release: 2.6.9-55.ELsmp
Version: #1 SMP Fri Apr 20 17:03:35 EDT 2007
Machine: i686
Instance name: repsrv
Redo thread mounted by this instance: 1
Oracle process number: 32
Unix process pid: 26218, image: oracle@repsrv.parazelsus.pk (J002)
*** 2011-07-12 00:02:47.977
*** SESSION ID:(264.3) 2011-07-12 00:02:47.977
*** CLIENT ID:() 2011-07-12 00:02:47.977
*** SERVICE NAME:(SYS$USERS) 2011-07-12 00:02:47.977
*** MODULE NAME:() 2011-07-12 00:02:47.977
*** ACTION NAME:() 2011-07-12 00:02:47.977
ORA-12012: error on auto execute of job 84
ORA-12005: may not schedule automatic refresh for times in the past
Why it is going to refresh at 00:02:47 though i defined to be refreshed at ROUND(SYSDATE) which becomes 12-jul-2011 00:00:00