Home / server / Questions / 208973
Accepted
DCookie
Asked: 2010-12-04 14:29:33 +0800 CST

How can I turn of redo logging on a materialized view refresh?

  • 772

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.

oracle

1 Answers

  1. Best Answer

    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.

    • 2