When executing the following code on the SCOTT schema I receive a compilation_error.
What am I doing wrong here? I doesn't even tell me what the error is.
SQL> create materialized view dept_emp_mv as (
2 select dname, job, hiredate, count(*) nbr
3 from emp e
4 join dept d on e.deptno = d.deptno
5 group by dname,hiredate,job);
Materialized view created.
SQL>
SQL> select staleness from user_mviews where lower(mview_name) = 'dept_emp_mv';
STALENESS
-------------------
FRESH
SQL>
SQL> insert into emp values (8003, 'TEST', 'TEST', 7902, to_date('20131127','YYYYMMDD'), 2500, null, 20);
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select staleness from user_mviews where lower(mview_name) = 'dept_emp_mv';
STALENESS
-------------------
NEEDS_COMPILE
SQL>
SQL> execute DBMS_SNAPSHOT.REFRESH('DEPT_EMP_MV','C', parallelism => 1);
PL/SQL procedure successfully completed.
SQL>
SQL> select staleness from user_mviews where lower(mview_name) = 'dept_emp_mv';
STALENESS
-------------------
COMPILATION_ERROR
Any help is appreciated.
Update
With Oracle 12c this issue seems to be resolved.
Best Answer
It seems that Oracle doesn't like ANSI-style joins in the materialized view definition when refereshing...
Changing the definition to
makes it work for me.
See http://sqlfiddle.com/#!4/f706b/1
Changing the definition in the SQL Fiddle back to the one included in the question will cause the
staleness
to beCOMPILATION_ERROR
.Personally, I would classify this as a work-around rather than an answer.