Re: Modify Rollback segment for backup process.

C Chang wrote:
>
> Sybrand Bakker wrote:
> >
> > "C Chang" <cschang_at_maxinter.net> wrote in message
> > news:3E6B775F.3294_at_maxinter.net...
> > > I have several audit type tables on my database. I am archiving them
> > > SAMI-ANNUALLY (did once). I transferred part of records into another
> > > temp table using
> > > CREATE TABLE..<archive_table> NOLOOGING
> > > TABLESPACE <anther tablespace>
> > > AS
> > > SELECT * FROM <the audit table>
> > > WHERE timeStamp < date1;
> > > Commit;
> > > Then
> > > DELETE FROM < the audit table> WHERE timeStamp < date1;
> > >
> > > Commit;
> > > Then back up the archive_table.
> > >
> > > Until now, I have not seen problem. But after reading about the
> > > rollback segment question on AskTom web site, it raises a question. My
> > > concern is that because the size of the records deleted is close to 80MB
> > > on average ( some goes about 130 MB), do I need to modify my rollback
> > > segment just for this cause ? In askTom site, Tom suggests that
> > > changing the rollback segment to largest expected size of transaction.
> > > Otherwise, I might get the "rollback segment can not extend ." error in
> > > the DELETE action. My current rollback segment is defined
> > >
> > > CREATE ROLLBACK SEGMENT R0
> > > TABLESPACE SYSTEM
> > > STORAGE (INITIAL 20K NEXT 16K MINEXTENTS 2 MAXEXTENTS 10);
> > > There are 10 rollback segments and all equal size.
> > >
> > > And the DB is 8.1.6 on NT 4 ( 2x 750Mhz CPU) with 5x 8G HD.
> > >
> > > C Chang
> >
> > 20k + 154k (9 times 16) is ridiculously low for any application.
> > You'd also better copy the rows you want to retain to another temporary
> > table and just TRUNCATE the original table. Will reset the HighWaterMark of
> > the table and absolutely NO redo.
> >
> > Hth
> >
> > --
> > Sybrand Bakker
> > Senior Oracle DBA
> >
> > to reply remove '-verwijderdit' from my e-mail address
> Sybrand:
> Thanks. I have already used the "CREATE TABLE <audit_temp>.. SELECT
> * FROM <original table> where timestamp < old_date;" to copy the
> records. But I can not use "TRUNCATE" to TRUNCATE the original table.
> I left some of latest records (timestamp >= old_date )in the original
> table. That's why I use the "DELETE..." and where is my concern came
> from. I might made a wrong statement of my rollback segment. Let me
> verify again.
>
> C Chang

I checked our DB again. The rollback segment is

   INITIAL 20M NEXT 10M MINEXTENTS 2 MAXEXTENTS 10 sorry for the mistake. My question is because of a one time big transaction, do we need to change the rollback segement?

C Chang Received on Mon Mar 10 2003 - 21:08:57 CST