ORA-12057: materialized view ... INVALID and must complete refresh
Hi Guys,
Help me on this...
Step 1:
create materialized view log on emp
with primary key, rowid(sal,deptno)
including new values;
Step 2:
create materialized view emp_a_mv
refresh fast on commit as
select count(*), sum(sal), deptno
from emp group by deptno;
Step 3:
select * from emp_a_mv;
COUNT(*) SUM(SAL) DEPTNO
---------- ---------- ----------
1 2300 40
1 4500 50
Step 4:
insert into emp values(10003,'Jegan',20000,10);
commit;
Step 5:
select * from emp_a_mv;
COUNT(*) SUM(SAL) DEPTNO
---------- ---------- ----------
1 2300 40
1 4500 50
1 20000 10
Step 6:
update emp set sal=25000 where empno=10000;
commit;
Step 7:
15:56:16 SQL> exec dbms_mview.refresh('EMP_A_MV');
BEGIN dbms_mview.refresh('EMP_A_MV'); END;
*
ERROR at line 1:
ORA-12057: materialized view "JEGAN"."EMP_A_MV" is INVALID and must complete
refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1
Can some help me on this... How to handle this?
Thnx.
Cheers!:confused: