Search code examples
oraclealter

Adding a new column to an existing table in production

What is the best approach to add a not null column with default value in production oracle database when that table contain one million records and it is live. Does it create any locks if we do the column creation , adding default value and making it as not null in a single statement?

Solution

  • Depends on the version. Starting from 11g, you can execute a simple ALTER TABLE command and there will be no impact, the default value is not materialized and taken from dictionary information.

    alter table tablename add columnname varchar2(2) default 'XX' not null;

    The above statement will not issue an update to all the records of the table [...] When a user selects the column for an existing record, Oracle gets the fact about the default value from the data dictionary [...] and still not incur any penalty for redo and undo generation [...]

    Source here, "Adding Columns with a Default Value" chapter