CODEWITHSUNDEEP
sqloracle-database
user15746603
user15746603

Reputation:

ORA-06512 when creating materialized view

I'm trying to create a mview in Oracle. It would be for a report I run everyday, so I would just need it updated before the execution, on demand.

CREATE MATERIALIZED VIEW mv_test
    REFRESH
        ON DEMAND
AS
    SELECT
        zzcode,
        COUNT(1) c
    FROM
        cdc.uap_zufi_t_dca_place@rbip
    GROUP BY
        zzcode;
        
BEGIN
    dbms_snapshot.refresh('mv_test', 'f');
END;

However, the below error appears:

ORA-06512: at line 2 23413. 00000 - "table "%s"."%s" does not have a materialized view log" *Cause: The fast refresh can not be performed because the master table does not contain a materialized view log. *Action: Use the CREATE MATERIALIZED VIEW LOG command to create a materialized view log on the master table.

Upvotes: 1

Views: 1461

Answers (1)

Justin Cave
Justin Cave

Reputation: 231861

You should really be using the dbms_mview package rather than the old dbms_snapshot. They do the same thing in this case but Oracle doesn't even bother documenting the dbms_snapshot package any longer.

The second parameter of dbms_mview.refresh is the method. You're specifying a method of 'f' which means that you're asking for a fast (incremental) refresh. If you want an incremental refresh, you'd need to create a materialized view log on the remote database (something you almost certainly cannot do over the database link). Alternately, you can ask for a complete refresh instead at the cost of sending every row over the network every time

begin
  dbms_mview.refresh( 'MV_TEST', 'c' );
end;

Upvotes: 1

Related Questions