Alter table in archive log mode database
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?
When everything is lost, future still remains.
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"
Another option is to put the database is noarchivelog mode, do your stuff and revert it back after the change.
Last edited by kris109; 01-29-2004 at 11:52 PM.
Remember the Golden Rule - He who has the gold makes the rules!
Ph.D., OCP 8i, 9i, 10g, 11g DBA
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.
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.
Click Here to Expand Forum to Full Width