Ways to eliminate undo on update?

My goal is reducing the precision and scale of a populated column on a table with almost 500 million rows.

I haven't tried the online redef yet (this is 10gR2), but the other option is taking a huge amount of undo. I have set ALTER SYSTEM SET UNDO_RETENTION=5; which helped quite a bit on the original full import.

Is there a way to disable undo for the update operation? I think my best bet would be doing CTAS nologging, drop old table and rename new one.

On 2/9/06, Richard Quintin <rquintin_at_vt.edu> wrote:
> If you are 9i+ you have two more options.
>
> Online redefinition would allow you to do it without any additional down time.
>
> or
>
> as an alternative to creating a new table you can do something like
> alter table foo add column(x number(7,3));
> update foo set x = old_column;
> alter table foo drop column old_column;
> alter table foo rename column x to old_column;
>
> I rather doubt this method would be more performant than just importing into a
> pre-created table, but it's an option...
>

--
http://www.freelists.org/webpage/oracle-l