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?
Additional information:
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;