1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

complete refresh of materialized view works fine, but non-complete doesn't

Discussion in 'SQL PL/SQL' started by ecivgamer, Mar 4, 2015.

  1. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Hi all,

    Complete refresh of materialized view works fine, but non-complete doesn't.

    Code (Text):
    begin
    DBMS_SNAPSHOT.REFRESH('SNAP_AP_PAY_SCHED','C');
    end;

    PL/SQL procedure successfully completed.
     

    begin
    DBMS_SNAPSHOT.REFRESH('SNAP_AP_PAY_SCHED');
    end;

    ORA-00942: table or view does not exist
    ORA-02063: preceding line from PLSDW
    ORA-02063: preceding 2 lines from PRODFIN.PLSPRO.COM
    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 line 2

    Script Terminated on line 1.
    I checked quantity of rows in source table and in destination materialized view.
    It was not equal before complete refresh and it became equal after complete refresh.

    I don't see this error on other materialized views.

    What does it mean and how to solve?
    Please tell me what additional information should I provide in order for you to help me.
    Thanks ahead.


    P.S.: Cross-posted from oracle discussion board, can't really find solution yet.
    https://community.oracle.com/thread/3680526?sr=inbox&ru=719145

     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    There is no 'non-complete' method, there is FAST refresh or COMPLETE refresh. The default is the method stored in the data dictionary for that view/snapshot. I expect you do not have materialized view logs for the base tables this MV is built on so a FAST refresh isn't possible.


    Post what the data dictionary has for this MV:


    select refresh_mode
    from dba_snapshots
    where name ='SNAP_AP_PAY_SCHED' :
     
  3. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Creating a sql trace is good idea and I'd love to do it to improve my experience. Unfortunately, it will require more access grants and team lead didn't approve it. So I will stick to recreating materialized view on weekend (unless I will receive better suggestion).