Re: MV refresh over database link hangs

On Feb 3, 12:53 pm, pratap.deshm..._at_gmail.com wrote:
> Hi,
>
> I have two databases, version 10.2.0.1.0 on the same machine.
> Source database - Has a table with MV log
> Target database - Has a MV with force refresh option.
>
> But when I refresh the MV, the refresh hangs and following wait events
> are shown -
>
> Source database - SQL*Net more data to client
> Target database - SQL*Net more data from dblink
>
> It just hangs for hours. Then when I drop and re-create the MV, the MV
> gets all data in 15 mins.
>
> Does anyone have a workaround for this problem?
>
> Regards,
> Pratap

I am not sure where to find the info but I read a post about slow MV refresh on 10.2 I am pretty sure. In this case the MV had worked well on a lower version. The problem was traced to a change in the Oracle logic where instead of using a truncate/insert to do the refresh as in the past Oracle is now using a delete/insert to refresh the data. The fix was to set a database parameter, probably underbar parameter, to restore the older but more effective logic. If you have a support contract you might open an SR and ask about any change in the refresh logic.

And there is also the change that you are having a different problem since the first item I rememger may not have been over a database link. Someone else may have other information.

Goodl luck
-- Mark D Powell -- Received on Sat Feb 03 2007 - 19:48:42 CST