ORA-12057: materialized view ... INVALID and must complete refresh

[balajiyes]

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!

[pando]

DBMS_SNAPSHOT.REFRESH (
{ list IN VARCHAR2,
| tab IN OUT DBMS_UTILITY.UNCL_ARRAY,}
method IN VARCHAR2 := NULL,
rollback_seg IN VARCHAR2 := NULL,
push_deferred_rpc IN BOOLEAN := TRUE,
refresh_after_errors IN BOOLEAN := FALSE,
purge_option IN BINARY_INTEGER := 1,
parallelism IN BINARY_INTEGER := 0,
heap_size IN BINARY_INTEGER := 0
atomic_refresh IN BOOLEAN := TRUE);


use CF for method

[balajiyes]

whtz CF method?
Thnx.

[pando]

sorry use C instead of CF

C == complete refresh
F == Fast refresh

[balajiyes]

Thnx Pando.
Cheers!

[balajiyes]

Hi Pando,

There is a SEQUENCE option in Materialized View Log?
Can u explain this.

Thnx in Advance.