You posted this question in the Oracle Learning Library space.
Are you using the Oracle Learning Library, or are you referring to a different Oracle product?
Also, could you please visit the Getting Started page for instructions on how to change your display name so you're not just a number?
Hello Jim, Thanks for your response. My question refers to Oracle Database Enterprise Edition. Not sure if this is the correct forum for that question.
Since your post is not about the Oracle Learning Library, I don't think this is the right space.
In the ACTIONS box on the top right of your post, you should be able to click on "Move", and then specify the correct subspace of Database so the right experts can see your question.
Thanks and good luck!
What is your database version ? Post the result of select banner from v$version. Oracle 11.2/12.1 has optimized DDL operations. https://www.oracle.com/technetwork/articles/database/ddl-optimizaton-in-odb12c-2331068.html
If the tests shows that the above is not meeting your downtime then you can try to use DBMS_REDEFINITION to do it online.
Better: first add the column, then update, then add the not null constraint.
There will be some downtime when adding the column because all the procedures referring that table directly (not in dynamic SQL) will get decompiled and need to be recompiled.
After adding the column you may update all that using the facilities of dbms_parallel_execute, thus not having one single transaction for 900M records. That will help you split the work in chunks of several thousands or hundreds of thousands records.
Is it a table used for OLTP? If it's for OLTP, then you may have some problems running such massive updates and I'd say that the chunks for dbms_parallel_execute should be rather small, like no more than a couple of thousand records.
If it's used for OLAP, then the chunks for dbms_parallel_exucute may be much larger, like 1M records per chunk.
I sau that because I fear one single update may possibly not finish the work.
After having updated the column add the not null constraint with novalidate, something like below:
alter table zzz_t add constraint nn_xcol check (x_col is not null) enable novalidate;
Starting from 11gR2 for adding new columns with DEFAULT values and NOT NULL constraint , the default value is stored in the data dictionary instead of updating the table column as a result especially for large tables the execution time is reduced.
In 12.1 that is further optimized.
Good that it is so. Didn't know that thing.
Thanks much Saubhik and Bede for your valuable input. this is much helpful. By the way, my DB is oracle 12.1 version.
Warm Regards, Lily.