Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

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

Comments

Thank you Chris!

asg, July 28, 2016 - 9:57 pm UTC

Our application is an OLTP system in which transactions are continuously happening throughout the day. The tables have around 1 million to 20 million records each and I am concerned whether there will have to be any downtime for DDL's!
Chris Saxon
July 29, 2016 - 1:17 pm UTC

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

Very useful, thanks again Chris!

A reader, August 01, 2016 - 12:14 pm UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.