1 Reply Latest reply: Oct 7, 2010 2:45 AM by JustinCave RSS

    alter table add column

    770799
      I have a few huge tables on oracle db (11gR2), and I need to perform some ddl actions on it...
      since they're on production database, I need to be sure what afects commands like ALTER TABLE ADD COLUMN..

      other reason for putting such question is there is DBMS_REDEFINITION package for wich we're not sure is it better to use it (instead of alter table..).

      I was doing ddl many times, but now I wanna be sure ;)
        • 1. Re: alter table add column
          JustinCave
          I'm not sure I understand what question you're asking.

          The effects of adding a column to a table will depend almost entirely on your application and the column being added. If you have code that does a SELECT * all over the place and fetches the data into multiple local variables rather than a record defined based on the table type, adding a column may cause the entire application to fail. If you have well written code, there may be no impact. Depending on the data type of the new column, the average length, and your table storage settings, adding a new column may cause massive row migration which will cause performance issues. Or it may have no impact on performance. Presumably, your test environment exists so that you can thoroughly test what actually happens in your environment with your code when you add a particular column to a particular table.

          The DBMS_REDEFINITION package is generally useful if your downtime window is not sufficient to apply the DDL (and any scripts to populate the column). It will take longer to make the change using DBMS_REDEFINITION, but the table will remain available for the application in the interim.

          Justin