ORA-12057: materialized view ... INVALID and must complete refresh
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

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

Hybrid View

  1. #1
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598

    Question 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!

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    whtz CF method?
    Thnx.
    Cheers!
    OraKid.

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    sorry use C instead of CF

    C == complete refresh
    F == Fast refresh

  5. #5
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Thnx Pando.
    Cheers!
    Cheers!
    OraKid.

  6. #6
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Hi Pando,

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

    Thnx in Advance.
    Cheers!
    OraKid.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width