Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

1 Answer 1

up vote 0 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

Your Answer

 
discard

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.