Re: Modify Rollback segment for backup process.

"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