Oracle Database Free

use case: Another value needs to be added to an enum that is used on a large table having Billions of rows/TB of data

Proposed Technical Solution:

1. record current constraint name
2. drop domain .. force preserve to allow the old constraint to be active during maintenance
3. create domain with the new value(s) added
4. alter table add the new domain/constraint with novalidate
5. alter table drop the old domain/constraint

QUESTION How do I do Step 4?

When I try, I get either ""ORA-03075: unexpected item DOMAIN in an out-of-line constraint" or "ORA-00907: missing right parenthesis".

DB Version 23.4 (VirtualBox)

Sample Code:

prompt SETUP
create domain workflows_d as enum (
  ready = 'ready',
  step_1 = 'step_1',
  step_2 = 'step_2',
  completed = 'completed',
  error = 'error'
);

create table workflow_steps (
  step_name workflows_d primary key
  ,other_data varchar2(50)
  ,added_date date default on null sysdate
);
insert into workflow_steps (step_name) values
  ( workflows_d.ready), ( workflows_d.completed),
  ( workflows_d.error ),
  ( workflows_d.step_1 ),( workflows_d.step_2 );
commit;

-- record name of domain constraint
select * from user_constraints where table_name = 'WORKFLOW_STEPS';
drop domain workflows_d force preserve;
create domain workflows_d as enum (
  ready = 'ready',
  step_1 = 'step_1',
  step_2 = 'step_2',
  step_3 = 'step_3', -- this is added
  completed = 'completed',
  error = 'error'
);
-- throws ORA-03075: unexpected item DOMAIN in an out-of-line constraint
alter table workflow_steps
  add constraint some_constraint_name domain workflows_d(step_name) novalidate;
-- throws ORA-01735: invalid ALTER TABLE option
alter table workflow_steps
  add domain workflows_d(step_name) novalidate;
-- throws ORA-00907: missing right parenthesis
alter table workflow_steps
  modify ( step_name domain workflows_d novalidate );
-- works but validates the data
alter table workflow_steps
  modify ( step_name domain workflows_d );