Oracle 10 Standard:
I have a number of materialized views built on pre-built tables.
These are fast refreshed from a remote database over a link from one master db.
They've generally been working fine, but sometimes the view needs re-creating when a database server is bounced.
Comparing the remote (replicated) table with the master table now shows there are a few records missing in the replicated table. Re-creating the materialized view doesn't fix this (or manually refreshing it). There are no errors shown with the jobs that fast refresh these.
What is the procedure to fix this? Do I need to drop & regenerate the materialized view log on the master table?
Ok, dropping and recreating the materialized view log did not fix this.
I suppose I can just copy the records manually, but I thought there'd be a more generic way to fix. If
complete refresh is the answer, I understand that it truncates the table first which is something I'd really like to avoid if possible.
As requested, here is the script that creates the materialized view:
CREATE MATERIALIZED VIEW AUTO_EVENT_DEFN ON PREBUILT TABLE USING INDEX TABLESPACE DATA1 PCTFREE 10 INITRANS 2 STORAGE ( INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS UNLIMITED BUFFER_POOL DEFAULT ) REFRESH NEXT SYSDATE + 1/(24*60) FAST WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT DISABLE QUERY REWRITE AS select aed_rid, name, description, max_check_freq_sec, max_result_freq_sec, header, footer, subject, html_format, report_group, trigger_event, max_lines, html_base_size, ins_or_update, active, valid, defn_type, cust_req, last_modified, modified_by, sum_cols, report_delay_secs, process_queue, sql_string, report_security, results_type, multi_loc from auto_event_defn@kitlive;