Alter table in archive log mode database

[Shivdeep]

Hi,

Recently we were altering a table which had quite some data to add a column with a default value. Now the database was in archive log mode and a lot of redo was being generated, causing the archiver directory to get filled up to 100% and you know what happened next.
Apart from the housekeeping of the archiver directory what is the other way of altering the table without housekeeping the archiver directory?

[slimdave]

Create a new table with the nologging attribute, that includes all the current columns plus the new column.

Then you can either replace the old table with the new one, or ...

truncate the old one, add a new column to it, set it to nologging, disable the indexes, and "insert /*+ append */ " the rows from the "new_table"

[kris109]

Another option is to put the database is noarchivelog mode, do your stuff and revert it back after the change.

[anandkl]

You can also do this

1.Just add the column
2.alter table emp modify (column_name default value);

this will just happen quite fast.

regards
anandkl

[chao_ping]

There is always tradeoff.
Convert from archivelog-noarchivelog-archivelog requires two time database restart and invalided all backup of that database.

Recreate that table via nologging etc need double space, and you need recreate all the index, constraints etc, this also takes a lot of time.

..