No there is no way to split refresh of 1 mview to many transactions.
However, I can give you some things to look at:
- if you are using refresh group and dbms_refresh.refresh, all mviews in
it are refreshed in one transaction (for data consistency). If this is
your case, try refreshing the big mview with dbms_mview.refresh instead
(but be aware of consistency of data - foreign keys, etc).
- if you changed "most" of the records, you may benefit from complete
refresh. Complete refresh makes "truncate" (which is DDL and commits) and
then insert for all rows in the mview. I've seen this to be better on
network traffinc if there are updates of "most" of the rows. It will be
better on rollback also (truncate is lightweight on undo, insert is quite
lighter than update also). Beware that the table is practicaly EMPTY until
the refresh completes, so user will not see any data. Also if you abort
the refresh (e.g. network problems) - you cannot rollback, because of the
truncate.
Complete refresh is made with dbms_mview.refresh(mview_name, 'C')
- try refreshing more often, when you have less data updted (this is
obvious, but depends on your BUSINESS needs)
Yavor
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 -- //www.freelists.org/webpage/oracle-l
-- //www.freelists.org/webpage/oracle-l
