Re: delete data from table without rollback/logging?

Steve Salvemini <steve.salvemini_at_adelaide.edu.au> wrote in message news:<3BBD190B.D3B36442_at_adelaide.edu.au>...
> Hi group, I'm running a cut down script on a copy of a live database to
> remove some 90%
> of the data.
>
> Currently I use delete statements to remove the data from 100's of
> tables.
> Another method I can use is to copy out 10% into a temp table, truncate
> the original and
> then copy the 10% back in, reducing the hit on the rollback segments.
>
>
> Regardless of which option I take, is there a faster option to delete
> and insert data without filling up the rollback segments.

copy the 10% to a table using 'insert /*+append*/ into x as select * from y', 'truncate table y'
and 'insert /*+append*/ into y select * from x'

is the fastest way to do this. Don't forget it creates 'holes' in the redolog files. Normally it is better to take a backup after this process is ready since a rollforward from the previous backup will and up with empty tables ...
(assuming >= oracle 8i)

Ronald.