RE: MVIEW on-demand refresh - rollback space issue
- From: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
- To: <mnavickas@xxxxxxxxx>, <Yavor_Ivanov@xxxxxxxx>
- Date: Wed, 22 Feb 2006 11:07:22 -0500
You could use Streams for replication (instead of MVs) - still Oracle
"native" solution, and works pretty good.
Igor
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mindaugas Navickas
Sent: Wednesday, February 22, 2006 11:00 AM
To: Yavor_Ivanov@xxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: MVIEW on-demand refresh - rollback space issue
Thank you Yavor,
Non of those options are applicable in our case - except may last one -
do an immediate refresh after bigger batch update - that's what we are
looking at now.
Another option we are looking at is to drop indexes on MV before doing
refresh and recreate after that. This way index updates will not use
rollback space.
Once again I hear suggestions from my colegues not to use Oracle
"native"
replication and implement a custom one. Probably they are right...
Regards
Mindaugas Navickas
--- Yavor Ivanov <Yavor_Ivanov@xxxxxxxx> wrote:
> 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
>
> On Tue, 21 Feb 2006 20:24:11 +0200, Mindaugas Navickas
> <mnavickas@xxxxxxxxx> wrote:
>
> > 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
Other related posts:
