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.
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.