Re: DBMS_REFRESH and Redo-Logging

From: Bradd Piontek <>
Date: Mon, 16 Feb 2009 08:05:43 -0600
Message-ID: <>

I have you changed the default behavior of the refresh? In 10g, it will do a "DELETE" and then an INSERT unless you turn atomic refresh off. I belive you'll have to modify the job to use dbms_mview.refresh and pass "atomic_refresh => FALSE".

It may be that all your redo is coming from the DELETE and the INSERT is nologging. A 10046 trace when calling the refresh should be able to verify this.

Bradd Piontek
  "Next to doing a good job yourself,

        the greatest joy is in having someone
        else do a first-class job under your
  • William Feather

On Mon, Feb 16, 2009 at 7:29 AM, <> wrote:

> Actually there are no indexes lying on the view.
> ------------------------------
> *Von:* []
> *Im Auftrag von *Eric Buddelmeijer
> *Gesendet:* Montag, 16. Februar 2009 13:17
> *An:* Boll, Jan-Hendrik
> *Cc:*
> *Betreff:* Re: DBMS_REFRESH and Redo-Logging
> Jan,
> You can reduce the amount of redo generation considerably by dropping any
> indexes before the refresh and recreate them (nologging) after the refresh.
> If you have any indexes on the MV at all and have not already done this.
> Kind regards,
> Eric.
> 2009/2/16 <>
>> Hi folks,
>> i've got a mv created with nologging-clause on 10204. This view
>> is regularly refreshed *completly *using dbms_refresh.refresh. During the
>> refresh a plenty of archive-logs are generated, flooding our
>> archive-destination.
>> As result oracle deactivates the full destionation forcing the database to
>> halt.
>> Error:
>> ARCH: Archival stopped, error occurred. Will continue retrying
>> ORACLE Instance dada - Archival Error
>> ORA-16014: log 1 sequence# 49531 not archived, no available destinations
>> After that the archiver is pausing for 2 or 3 minutes.
>> As i know that there is no way to prevent redo-log generation, do you know
>> a workaround for such a scenario? Expect increasing file-systemspace and
>> deactivating archivelog mode. :-)
>> And secondly: how am i able to prevent oracle from waiting for the
>> archiver. Is there any way to reenable the deacitvated
>> archive_destionations?
>> regards, jan

Received on Mon Feb 16 2009 - 08:05:43 CST

Original text of this message