Re: Modify Rollback segment for backup process.

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 Received on Sun Mar 09 2003 - 22:34:01 CST