Oracle DBMS_REDEFINITION: Unresolved Referential Constraint Transfer During Online Redefinition

Direct Answer

Oracle DBMS_REDEFINITION does not preserve the enabled/disabled state or deferrability of referential constraints. No constraints of any kind - referential, check, unique, or primary key - are copied from the source table to the interim table. COPY_TABLE_DEPENDENTS with copy_constraints=>TRUE copies only indexes, triggers, grants, and statistics; constraints are explicitly excluded in Oracle 11gR2 through 23c. After FINISH_REDEFINITION_TABLE, the new table has zero constraints, and you must recreate them manually with the desired attributes.

Confirmed Facts vs. Behavior

Confirmed: Constraint deferrability (DEFERRABLE, INITIALLY DEFERRED) and enabled/disabled status are not transferred because the interim table starts without constraints. The redefinition state machine (START to SYNC to FINISH) does not interact with deferrability; constraints exist only on the source table during redefinition.

No silent failure: Constraint absence is explicit. Storage or undo problems raise errors rather than failing silently: a full SYSAUX raises ORA-12034 or ORA-01658, and insufficient undo raises ORA-01555 during SYNC_INTERIM_TABLE.

Storage and Undo Conditions

• SYSAUX / MV log: The materialized view log (MLOG$_) grows in SYSAUX based on DML volume on the source table during the redefinition window, not table size. SYSAUX must have free space or autoextend enabled.
• Undo retention: Must cover the full redefinition duration plus any flashback query needs. Use GUARANTEE UNDO RETENTION or set UNDO_RETENTION high enough for the window.

For small-scale workloads completing in minutes, SYSAUX and undo are typically non-issues. Monitor DBA_SEGMENTS for MLOG$_% segments and V$UNDOSTAT during the run.

Undocumented Initialization Parameters

None exist that influence constraint migration. Hidden parameters such as _disable_logging or _redefinition_debug affect only logging or tracing. Constraint exclusion is hardcoded.

Required Steps

1. Run DBMS_REDEFINITION.CAN_REDEF_TABLE to confirm eligibility.
2. Capture source constraint DDL (DBMS_METADATA.GET_DDL or USER_CONSTRAINTS/USER_CONS_COLUMNS) before starting.
3. Complete START_REDEFINITION_TABLE to SYNC_INTERIM_TABLE to FINISH_REDEFINITION_TABLE.
4. After FINISH_REDEFINITION_TABLE, recreate constraints on the new table with the desired enabled/disabled and deferrability attributes. Use ENABLE VALIDATE for immediate validation or ENABLE NOVALIDATE for deferred validation.

Verification Commands

SELECT dbms_metadata.get_dependent_ddl('CONSTRAINT', 'SOURCE_TABLE', 'SCHEMA') FROM dual;
SELECT constraint_name, constraint_type, status, deferrable, deferred
FROM user_constraints WHERE table_name = 'SOURCE_TABLE';
SELECT segment_name, bytes/1024/1024 mb
FROM dba_segments WHERE segment_name LIKE 'MLOG$_%' AND tablespace_name = 'SYSAUX';
SELECT begin_time, end_time, undoblks, maxquerylen
FROM v$undostat ORDER BY begin_time DESC FETCH FIRST 10 ROWS ONLY;

One Diagnostic Detail Needed

What is the typical DML rate (rows per second) on the source table during the planned redefinition window? This determines whether SYSAUX autoextend or undo retention will be a practical constraint for your workload.