Add a column with DEFAULT VALUE

This is another tricky example that worth your attention. When you adding a column with a DEFAULT value specified, then this default value, and not NULLS, will be populated in every row of a new column. Frankly speaking, it is not quite true, because starting from version 11.2, Oracle won’t populate all these values within the table,  it rather stores the default value in the metadata and simply shows you it to you by making the column addition instantaneous while preventing massive update on the entire table. It also prevents it from generating tons of undo and redo. Below we have an order_items table

Let’s add a new column with DEFAULT value to this table

Now, let’s have what have been populated in the new column.


As we can see SYSDATE function result has been populated (read shown) as a DEFAULT value for every row in this new column. Not NULLs. Keep it in mind, because exam has this trick and many students are willing to think that NULL will be populated.

Remember, you can’t do a ROLLBACK now because ALTER TABLE  did COMMIT; To remove this column you have to do

alter table order_items drop column update_dt;

The exam may contain a specific question about it. You need to know that the right answer for Oracle behavior while adding a new column with DEFAULT value will be “adding DEFAULT value to metadata”

Leave a comment

Your email address will not be published. Required fields are marked *