alter table to add default takes too long time
Posted on 2003-11-26
Is there anything I can do to expedite adding a default to a table?
We have an on-line database that I want to set a default. I made an off-line of a table MyTable and changed the default of one field to 'Active'
Alter table ep_dependents add default 'Active' for dep_status
It took 1 second to change this default on the copy, so I thought I could relatively do the same thing in production. I tried to run it in production and let it go half an hour before I realized it was still trying to complete and I probably denied some users while this was attempting to process.
any recommendation? It is probably best to take the DB off-line in the middle of the night, but I also wonder if there is a way to do this on the fly even if I have to knock users off for a minute...