Oracle : Enable Default Constraint on Table with Millions Records

Recently I got request from development team to add a  new column to an existing table with default value of sysdate.
This table has over 88 million records. if I add default constraint to that table directly, it will acquire exclusive table
lock on table and table is not be acessible for any activity during this operation.
Update this much records can take day(s) to complete, so we can't do this directly on production environment.

Following can be our checklist
1. Add column to table without default constraint
2- backfill new column
3- Enable default on new column

I have developed logic to select a chuck of record and update them.even this chunk can cause transactional locks, os I have
added logic to commit every 5000 records. Now I am in situation to execute it on any environment.




we can use CTAS as an alternate option, I will cover that in my next post.


sqlplus user/pwd;
-----------------------------------------
--Creating test table
-----------------------------------------

CREATE TABLE MyUpdtest
  ( id NUMBER,
    name varchar2(100)
  );
 

-----------------------------------------
--Populating test table
-----------------------------------------
declare
varcount number := 0;
BEGIN
  FOR t IN 1..4000000
  LOOP
  INSERT INTO MyUpdtest
  VALUES (t, 'Employee'||t );
  varcount := varCount+1;
  IF mod(varCount, 5000) = 0 THEN -- Commit every 5000 records
    COMMIT;
  END IF;
END LOOP;
END;
/

-----------------------------------------
--1- Add new column
-----------------------------------------

ALTER TABLE MyUpdtest ADD CreateDate DATE;

-----------------------------------------
--2- backfill new column
-----------------------------------------
/*
Following is the core script to update records.It is suggested to transform it in shell script and schedule to execute in
off-peak hours.
*/
spool on
spool '/loc/upd.txt' -- set spool location accordingly

SELECT TO_CHAR(sysdate,'dd/mm/yyyy HH:MM:SS') AS StartTime FROM dual;

select count(*) as totalRowsToProcess  from MyUpdtest where CreateDate is null;
declare
varcount number := 0;
varcount2 number := 0;
varDte date;
begin
--setting counter to set loop condition
SELECT COUNT(*) INTO varcount2 FROM MyUpdtest WHERE CreateDate IS NULL;
-- setting current date portion only
SELECT to_date(TO_CHAR(sysdate,'dd/mm/yyyy'),'dd/mm/yyyy')
  INTO varDte
  FROM dual;
WHILE varcount2 > 0 --quits if 0 or less
LOOP                -- while loop start
  FOR r IN
        (SELECT ID,
          Name
        FROM MyUpdtest
        WHERE CreateDate IS NULL
        AND rownum        <200001
        ) --limitint batch to 200000
  LOOP
    UPDATE MyUpdtest
    SET CreateDate         = varDte
    WHERE Id             = r.id
    AND name           =r.name;
    varcount              := varCount+1;
    IF mod(varCount, 5000) = 0 THEN -- Commit every 5000 records
      COMMIT;
    END IF;
  END LOOP;-- for loop ends
  COMMIT;
  varcount2:=varCount2-200000;
END LOOP; --ends while loop
END;
/
select to_char(sysdate,'dd/mm/yyyy HH:MM:SS') as EndTime from dual;
spool off

-----------------------------------------
--3- Enable Default
-----------------------------------------
ALTER TABLE MyUpdtest MODIFY CreateDate DEFAULT sysdate;