Thanks for the question, asg.
Asked: July 28, 2016 - 3:36 pm UTC
Last updated: July 29, 2016 - 1:17 pm UTC
Version: Oracle 12c
Viewed 1000+ times
You Asked
Hi Tom
I have read in the below link that DDL will not require any downtime starting from Oracle 11g (understood that same applies to Oracle 12c):
http://www.oracle.com/technetwork/database/features/online-ops-087977.html
I am using Oracle 12c. I would like to know if there is any optimum limit to set this DDL_LOCK_TIMEOUT parameter? The less time we set here, say 10 seconds, if the DDL is not performed I will have to execute this DDL again. Else is it better and safe to set this parameter to 3600 seconds assuming that in the next 1 hour I will have my column added!!
and Chris said...
Uncommitted DML (inserts, updates, deletes) will block DDL statements.
So the question is:
How long do you expect the transactions running when you execute your DDL to be active (uncommitted) for?
If you have updates that take 10s, you'll want your timeout to be at least 10s. Otherwise it could timeout before the update finishes!
No matter how high you set the value, you could always timeout. Setting it to one hour "just-in-case" seems excessive to me.
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment
It's only in-progress transactions that will block the DDL. Any new transactions you start after issuing "alter table ..." will work as normal and allow the DDL to complete.
So if you're in a "true" OLTP system, you should be able to keep the timeout reasonably low.
If you're wanting zero downtime upgrades I strongly recommend you check out Edition-Based Redefinition (EBR):
https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_editions.htm#ADFNS020
https://oracle-base.com/articles/11g/edition-based-redefinition-11gr2
http://www.oracle.com/technetwork/database/features/availability/edition-based-redefinition-1-133045.pdf
