1

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;
  • Can you share the materialized view so i can check from my side – moudiz Oct 23 '18 at 13:31
1

And this is exactly what happens when you create materialized views on a prebuilt table that is already stale.

Recreating the mview the same way will not solve this, why would it? You still use the same incomplete prebuilt table.

Recreating mview logs has nothing to do with it. If you recreate mview logs now, that does not include data that was modified in the past.

Yes, the solution is complete refresh, and that does not necessarily truncate the table.

REFRESH Procedures

DBMS_MVIEW.REFRESH has a parameter called atomic_refresh which defaults to true.

A complete refresh with atomic_refresh set to true refreshes all mviews given as parameter in a single transaction. It is achieved by DELETE-ing the rows from the mview.

A complete refresh with atomic_refresh set to false refreshes all mviews given as parameter in seperate transactions. It is achieved by TRUNCATE-ing the mviews (which is much quicker than DELETE).

So specify atomic_refresh => true, and the mview will not be truncated.

  • First off, can you point to where in that link (or elsewhere) it states (or infers) the truncate vs delete behaviour? I couldn't find that. I did state that I didn't want the mview truncated, but the reason was really that I didn't want other connections looking at the table at the same time to read an empty table. If I understand this correctly, then that isn't the case either way...both the delete/refresh, and truncate/refresh happen in a transaction, so other connections will either just see the table before the transaction began, or after - never in the middle. Is this correct? – Gerrat Oct 23 '18 at 17:29
  • @Gerrat Typical Oracle, removing useful parts from the documentation of newer versions. docs.oracle.com/cd/A87860_01/doc/server.817/a76994/… - Use atomic=FALSE, which will use TRUNCATE instead of DELETE to delete existing rows.. 11.2: docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_mview.htm - As part of complete refresh, if truncate is used (non-atomic refresh) .... – Balazs Papp Oct 23 '18 at 18:27
  • @Gerrat TRUNCATE is a DDL statement, DDL statements perform implicit commits, ending the transaction. So the mview is truncated, the transaction ends with a commit, and other sessions see an empty mview, yes. But DELETE is DML, DELETE FROM ... and INSERT INTO ... can be in the same single transaction, so other sessions still see the old content of the mview even while it is being refreshed. – Balazs Papp Oct 23 '18 at 18:32
  • Ok, great. Thanks for all the clarification & links. ..and nice to know exactly how the truncate differs from delete (other than just not generating REDO). – Gerrat Oct 24 '18 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.