Alter table in archive log mode database Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Alter table in archive log mode database

  1. #1
    Join Date
    Oct 2003
    New Delhi

    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.

  2. #2
    Join Date
    Aug 2002
    Colorado Springs
    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"
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    May 2001
    San Francisco, California
    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 10:52 PM.
    Remember the Golden Rule - He who has the gold makes the rules!
    Ph.D., OCP 8i, 9i, 10g, 11g DBA

  4. #4
    Join Date
    Aug 2001
    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.


  5. #5
    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width