M_View failed inrefresh throws error: ORA-12008/ORA-01843:not a valid month
787789Jul 19 2012 — edited Jul 19 2012I am not able to refresh this matrialized view in 2 databases.Can some one help please
======================================================
SQL> exec dbms_refresh.refresh('OID_SYNCH.OID_SYNCH_EMPLOYEES_HR');
BEGIN dbms_refresh.refresh('OID_SYNCH.OID_SYNCH_EMPLOYEES_HR'); END;
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01843: not a valid month
ORA-02063: preceding line from HR
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2251
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2457
ORA-06512: at "SYS.DBMS_IREFRESH", line 685
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
======================================
DDL of Matrialized view is as:
CREATE MATERIALIZED VIEW "OID_SYNCH"."OID_SYNCH_EMPLOYEES_HR" 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)
TABLESPACE "OID_SYNCH_D" BUILD IMMEDIATE USING INDEX REFRESH COMPLETE ON DEMAND START WITH sysdate+0 NEXT trunc(sysdate + 1) USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE AS ( select hr_person_id, hr_employee_status, hr_employee_number,hr_workbrain_id, hr_last_name, hr_first_name, hr_full_name,
hr_known_as, hr_middle_names, hr_previous_last_name,hr_suffix, hr_email_address, hr_user_name,system_person_type, user_person_type, hr_start_date, hr_last_update_date,home_city, home_state, home_zip, home_country,home_last_update_date,position_name, job_name,work_location_code, work_location_desc,work_city, work_state, work_zip,work_country,work_last_update_date,hr_supervisor_name,hr_supervisor_id,hr_sup_last_update_date,termination_date
from oid_synch_employees@hr )