In Oracle I need to add a new column with type timestamp with time zone to a large table (80 million rows). I cannot have more than 30 - 60 seconds of downtime when I run it in production.
This statement completes instantly:
alter table my_table add my_timestamp_col timestamp(6) with time zone;
However I would like to default the column to current_timestamp at time zone 'UTC' and force it be non-null.
With other data types, Oracle 11g will do this quickly if you use "add column default not null" For example both of these complete instantly:
alter table my_table add my_varchar_col varchar2(10) default 'hello' not null; alter table my_table add my_date_col date default sysdate not null;
But with a timestamp it is slow (at least tens of minutes on my test DB). I assume this means it is updating every row.
So my questions are:
- Why is it different with a timestamp than other data types?
- Is there a fast way to accomplish the outcome where the column is forced to be non-null and new rows default to the current UTC timestamp?
Version: Oracle Database 11g Enterprise Edition Release 184.108.40.206.0 - 64bit Production