Online Redefinition, Edition-Based Redefinition Concepts, Expand/Contract, and Lock-Aware DDL

Learning outcomes

ServiceHub must add a derived priority column and reorganize a busy table without a multi-hour copy outage. A direct CTAS/rename would stop writers or lose concurrent changes. Oracle DBMS_REDEFINITION keeps an interim table synchronized while the original remains available for most of the operation, then performs a brief final cutover. Edition-Based Redefinition (EBR) solves a related but different problem: multiple versions of application schema/code can coexist during a release.

1. Online table redefinition is a synchronized shadow-table mechanism

Oracle creates/maintains internal materialized-view infrastructure so changes to the original table can be synchronized into an interim table. The interim table carries the desired post-change structure/storage. At FINISH_REDEF_TABLE, Oracle swaps the definitions/dependencies in a short lock window.

2. Required privileges are narrower than SYSDBA

For redefining a table in the user's own schema, current documentation requires CREATE TABLE and CREATE MATERIALIZED VIEW; copying dependent triggers also needs CREATE TRIGGER. The package runs with invoker rights. Use the application owner or a controlled migration principal, not permanent SYSDBA application automation.

GRANT CREATE MATERIALIZED VIEW, CREATE TRIGGERTO servicehub_owner;GRANT EXECUTE ON SYS.DBMS_REDEFINITIONTO servicehub_owner;

3. Deliberate failure: primary-key mode on a table with no key

CREATE TABLE servicehub_owner.sh28_no_pk (  work_order_id NUMBER,  status_code VARCHAR2(12));BEGIN  DBMS_REDEFINITION.CAN_REDEF_TABLE(    uname        => 'SERVICEHUB_OWNER',    tname        => 'SH28_NO_PK',    options_flag => DBMS_REDEFINITION.CONS_USE_PK  );END;/-- Expected:-- ORA-12089: cannot online redefine table ... with no primary key

The safe repair is normally to define a real key that matches the data model. A rowid-mode redefinition exists for eligible tables, but choosing it only to bypass poor key design can introduce different restrictions and hidden M_ROW$$ handling.

4. Create an eligible source table and interim shape

BEGIN EXECUTE IMMEDIATE  'DROP TABLE servicehub_owner.sh28_orders_int PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/BEGIN EXECUTE IMMEDIATE  'DROP TABLE servicehub_owner.sh28_orders PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/CREATE TABLE servicehub_owner.sh28_orders (  work_order_id NUMBER PRIMARY KEY,  status_code VARCHAR2(12) NOT NULL,  amount NUMBER(12,2) NOT NULL);INSERT INTO servicehub_owner.sh28_orders VALUES(1,'OPEN',120);INSERT INTO servicehub_owner.sh28_orders VALUES(2,'CLOSED',80);COMMIT;CREATE TABLE servicehub_owner.sh28_orders_int (  work_order_id NUMBER,  status_code VARCHAR2(12) NOT NULL,  amount NUMBER(12,2) NOT NULL,  priority_label VARCHAR2(10) NOT NULL);

5. Precheck then start with rollback enabled

BEGIN  DBMS_REDEFINITION.CAN_REDEF_TABLE(    uname        => 'SERVICEHUB_OWNER',    tname        => 'SH28_ORDERS',    options_flag => DBMS_REDEFINITION.CONS_USE_PK  );END;/BEGIN  DBMS_REDEFINITION.START_REDEF_TABLE(    uname           => 'SERVICEHUB_OWNER',    orig_table      => 'SH28_ORDERS',    int_table       => 'SH28_ORDERS_INT',    col_mapping     =>      'work_order_id work_order_id,       status_code status_code,       amount amount,       CASE WHEN amount >= 100            THEN ''HIGH'' ELSE ''NORMAL'' END priority_label',    options_flag    => DBMS_REDEFINITION.CONS_USE_PK,    enable_rollback => TRUE  );END;/

ENABLE_ROLLBACK=TRUE preserves the ability to roll back after FINISH while keeping later DML. If you merely need to cancel before FINISH, ABORT_REDEF_TABLE is the cleanup path.

6. Copy dependent objects and inspect errors

DECLARE  l_errors PLS_INTEGER;BEGIN  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(    uname            => 'SERVICEHUB_OWNER',    orig_table       => 'SH28_ORDERS',    int_table        => 'SH28_ORDERS_INT',    copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS,    copy_triggers    => TRUE,    copy_constraints => TRUE,    copy_privileges  => TRUE,    ignore_errors    => FALSE,    num_errors       => l_errors,    copy_statistics  => TRUE  );  DBMS_OUTPUT.PUT_LINE('copy errors='||l_errors);END;/

Do not continue blindly when dependency copy reports errors. Check redefinition error views/package diagnostics and manually register/create unsupported dependencies as documented.

7. Prove concurrent DML is captured

INSERT INTO servicehub_owner.sh28_ordersVALUES(3,'OPEN',250);UPDATE servicehub_owner.sh28_ordersSET amount=95WHERE work_order_id=1;COMMIT;BEGIN  DBMS_REDEFINITION.SYNC_INTERIM_TABLE(    uname      => 'SERVICEHUB_OWNER',    orig_table => 'SH28_ORDERS',    int_table  => 'SH28_ORDERS_INT'  );END;/SELECT *FROM servicehub_owner.sh28_orders_intORDER BY work_order_id;

Expected interim state includes row 3 and the updated amount for row 1, with priority labels generated by the mapping. SYNC reduces final catch-up work; it does not itself perform the metadata cutover.

8. Finish in a controlled low-conflict window

BEGIN  DBMS_REDEFINITION.FINISH_REDEF_TABLE(    uname      => 'SERVICEHUB_OWNER',    orig_table => 'SH28_ORDERS',    int_table  => 'SH28_ORDERS_INT'  );END;/DESC servicehub_owner.sh28_ordersSELECT *FROM servicehub_owner.sh28_ordersORDER BY work_order_id;

The original logical table name now exposes the new definition. FINISH requires a brief lock/cutover window; high DML can delay it. Measure and rehearse the final lock window instead of calling the whole operation literally zero downtime.

9. Post-finish rollback preserves later DML

UPDATE servicehub_owner.sh28_ordersSET amount=333,    priority_label='HIGH'WHERE work_order_id=2;COMMIT;

BEGIN  DBMS_REDEFINITION.ROLLBACK(    uname      => 'SERVICEHUB_OWNER',    orig_table => 'SH28_ORDERS',    int_table  => 'SH28_ORDERS_INT'  );END;/DESC servicehub_owner.sh28_ordersSELECT *FROM servicehub_owner.sh28_ordersORDER BY work_order_id;

The original pre-redefinition structure returns while DML made after FINISH is preserved according to the rollback mechanism. If the new structure is accepted, use ABORT_ROLLBACK to end the rollback window and clean retained rollback machinery.

10. Abort before cutover versus rollback after cutover

Test the exact path in staging. Space consumption can approach another copy of the table plus indexes/logging.

11. EBR solves application-version coexistence, not table-copy alone

Edition-Based Redefinition (EBR) creates database editions so editioned objects such as PL/SQL code can have old/new definitions concurrently. Tables themselves are noneditioned. Table-shape evolution uses editioning views to present different logical column shapes and, while old/new applications write simultaneously, forward/reverse crossedition triggers transform data between representations.

SELECT edition_name,parent_edition_name,usableFROM dba_editionsORDER BY edition_name;SELECT username,editions_enabledFROM dba_usersWHERE username='SERVICEHUB_OWNER';

Do not enable editions or introduce crossedition triggers casually. EBR is an application architecture with object-type restrictions, edition grants/services and explicit retirement of old editions.

12. Expand/contract is the common denominator

Whether you use plain DDL, DBMS_REDEFINITION or EBR, a safe rolling application release typically expands compatibility first, migrates/backfills, deploys new readers/writers, validates, drains old sessions/pools, then contracts old structures in a later release. This directly continues Chapter 27's deployment coordination.

13. Cleanup and production judgment

BEGIN  DBMS_REDEFINITION.ABORT_REDEF_TABLE(    uname      => 'SERVICEHUB_OWNER',    orig_table => 'SH28_ORDERS',    int_table  => 'SH28_ORDERS_INT'  );EXCEPTION  WHEN OTHERS THEN    IF SQLCODE NOT IN (-23539,-23540) THEN RAISE; END IF;END;/DROP TABLE servicehub_owner.sh28_orders_int PURGE;DROP TABLE servicehub_owner.sh28_orders PURGE;DROP TABLE servicehub_owner.sh28_no_pk PURGE;

Online redefinition is included in Free and needs no management pack, but it needs schema privileges, temporary space, supported object shape and a final lock window. EBR is a different release-management architecture for editioned application objects and table compatibility views/triggers. Keep abort/rollback tested and inspect invalid dependencies before switching traffic.

Lesson 5 combines these ideas with platform/cloud movement, final synchronization and a service/DNS cutover where GoldenGate can reduce downtime only when separately licensed and correctly operated.

Authoritative references

• DBMS_REDEFINITION — current package API including rollback
• Managing Tables — Online Redefinition — privileges, restrictions and workflow
• Using Edition-Based Redefinition — editions/views/crossedition triggers
• ORA-12089 — no-primary-key failure
• Licensing Information — Free online table redefinition availability