Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

10gR2 Database, in Data Guard configuration to both a physical and logical standby.

I have a materialized view that does a complete refresh periodically of about 40K records from a remote database. It's been working on an hourly refresh for years without issue. Yesterday I was asked to make the refresh rate considerably more frequent, like, every 2 minutes. I did that, no apparent problems, until this morning around 4am the archive log directory filled up.

I've been struggling all day with trying to turn off logging. I have:

  • set NO FORCE LOGGING in the database
  • altered the materialized view and base table to NOLOGGING
  • altered the indexes on the view to NOLOGGING

Nothing seems to reduce the log generation rate. The view is refreshed in a scheduled task via dbms_refresh.refresh right before the procedure that reads it is kicked off.

I know I should revamp the process, probably by sucking the data into a global temp table each time the job runs, but that's going to take a while to get through the QA mill. Any insight as to what I am missing here? From what I can read it seems I should be able to do this. This asktom article seems to support that, but I can't seem to make it work.

Thanks so much for your time.

share|improve this question
up vote 2 down vote accepted

Ahh, I've stumbled onto the solution. Instead of dbms_refresh, use dbms_mview thusly:

begin dbms_mview.refresh('MYTABLE', method => 'C', atomic_refresh=>false); end;

I found the solution here.

Thanks for looking.

share|improve this answer
by setting the atomic_refresh to false, you could have read consistency issues, as why the mechanism was changed in the first place. – user259907 Dec 16 '14 at 3:00

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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