Oracle - Adding columns to a table takes forever

here's how I solved the problem. Before, I was specifying a default value for the columns right at the moment of adding them. But then I first added the columns with no default values. After columns got added I specified the default value and it executed immediately with no more waiting. Thank you very much @Justin Cave for your hint about Default value. That was the key point.

I have no doubt that it's related with the fact that when specifying default value at the time of adding column that default value is written to all the records inserted earlier.So if there are 5 million records in the table, that table will be updated to set default value for the newly added column for all the rows. Updating 5 million records is expensive as one might guess. But if default value is set after adding a column then the value of that new column in the rows inserted earlier will be NULL, so no update will take place.


I was also facing this problem when adding a varchar2(4000) column with no default value one a 61K row table in Oracle 11g. After 30 minutes the column was still not added.

I tried to abort the operation, and retried it, but it still didn't finish.

What worked:
I tried adding a number column instead, that took a split second. Dropped it.
Then i tried adding a varchar2(1) column, that took a split second. Dropped it.
Then i tried adding a varchar2(4000) column again, and it only took a split second.

Silly "workaround", but maybe this also works for anyone else facing this problem.