MVIEW on-demand refresh - rollback space issue

From: Mindaugas Navickas <mnavickas_at_yahoo.com>
Date: Tue, 21 Feb 2006 13:24:11 -0500 (EST)
Message-ID: <20060221182411.47359.qmail@web30104.mail.mud.yahoo.com>

Hi,

Platform Oracle 9.2.0.5 on HP-UX
Error(s):

ORA-12008: error in materialized view refresh path
ORA-01562: failed to extend rollback segment number 2
ORA-01650: unable to extend rollback segment RBS_1 by 125 in tablespace

ROLLBACK A big table is updated during a day on primary location. This table has MV log created - using PK.
Once a day changes to this table has to be propagated to a remote site (on-demand replication based on PK).

Most of times replication process works fine, however, there are times when larger number of rows are updated. Then replication fails with errors above. I know that most straightforward way would be to increase rollback tablespace size on remote site, however, we are short of disk space on that site and growing rollback tablespace (already 1.2G) just to accommodate this case it doesn't makes much sense (most of transactions on that database are small).

<b>Is there a way of splitting replication transaction into number of transactions?</b>

May be I am looking for something what does not exists, however, I hope, that others might faced similar problem and can share how they solved that.

Thank you
Mindaugas Navickas
DBA, OCP 10g, 9i, 8i

Find your next car at http://autos.yahoo.ca

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Feb 21 2006 - 12:24:11 CST