Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2015
    Posts
    1

    Unanswered: Materialized View Refresh Issue

    Hi All,
    We have batch script to refresh the materialized view automatically on 1st of every month.

    We got the error below during the MV refresh yesterday (1st of Nov, 2015).

    ERROR at line 1:
    ORA-12057: materialized view "XXX_DB"."XXX_MV" is INVALID and must
    complete refresh
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2809
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2994
    ORA-06512: at "XXX_DB.LOAD_MV_MONTHLY", line 251
    ORA-06512: at line 1

    Version: Oracle 11g
    Recently our Oracle data warehouse has been migrated into Super Cluster pltform.

    Could anyone please help on this urgently.

    Thanks in advance

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Did you try looking up your error? Seems pretty straight forward once you do.

    Please see the following link;

    http://ora-12057.ora-code.com/

    +"+
    +ORA-12057:materialized view "string"."string" is INVALID and must complete refresh+
    +Cause:The status of the materialized view was INVALID and an attempt was made to fast refresh the materialized view.+
    +Action:Perform a complete refresh of the materialized view. Check the value of the STATUS column in dba_mviews, all_mviews, or user_mviews to verify that the materialized view is VALID after the complete refresh.+
    +"+

    To compile the MV;

    http://ss64.com/ora/mview_a.html

    +"+
    +Syntax:+

    +ALTER MATERIALIZED VIEW [schema.]mview options iot_options+
    +[USING INDEX index_options]+
    +[REFRESH [refresh_options]]+
    +[COMPILE | CONSIDER FRESH | {ENABLE|DISABLE} QUERY REWRITE]+

    +ALTER MATERIALIZED VIEW [schema.]mview options iot_options+
    +[USING INDEX index_options]+
    +[REBUILD]+
    +[COMPILE | CONSIDER FRESH | {ENABLE|DISABLE} QUERY REWRITE]+

    +ALTER MATERIALIZED VIEW [schema.]mview options iot_options+
    +[USING INDEX index_options]+
    +MODIFY SCOPE FOR (ref_column/attribute) IS [schema.]scope_table+
    +[COMPILE | CONSIDER FRESH | {ENABLE|DISABLE} QUERY REWRITE]+
    +"+
    Dave

Posting Permissions

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