Schema Changes Without the Downtime: Using Oracle DBMS_REDEFINITION

The Problem: The 'Alter Table' Lock-out

You need to make a structural change to a massive production table—perhaps adding a column with a default value, changing the storage compression, or reorganizing a fragmented heap. In a standard scenario, an ALTER TABLE ... MOVE or a heavy ALTER statement can lock the table for an extended period, effectively taking your application offline.

The takeaway: DBMS_REDEFINITION allows you to rebuild a table's structure and migrate its data while the application continues to perform INSERTs, UPDATEs, and DELETEs. Instead of locking the table, Oracle manages the changes in an interim table and synchronizes the delta at the end.

How Online Redefinition Works

Online redefinition is not a single command but a workflow. It creates a \"shadow\" or interim table that matches your desired final state. While Oracle copies the bulk of the data from the original table to the interim one, it tracks all concurrent DML (Data Manipulation Language) changes in a materialized view log. Once the bulk copy is done, these logs are applied to the interim table, and a brief metadata swap occurs to make the interim table the primary one.

Pre-flight Checks

Not every table can be redefined online. For example, tables containing LONG columns or certain complex partitioning schemes may be unsupported. Before committing to a production window, you must verify eligibility.

-- Run this in a PL/SQL block to check eligibility\nBEGIN\n  DBMS_REDEFINITION.CAN_REDEF_TABLE('APP_USER', 'ORDERS');\nEND;\n/

If this procedure completes without an exception, the table is a candidate. If it fails, check DBA_REDEFINITION_ERRORS for the specific reason.

Worked Example: Adding a Column and Changing Compression

Assume we have a table ORDERS in the APP_USER schema. We want to add a REGION_CODE column and enable ROW STORE COMPRESS ADVANCED to save space, all without stopping the application.

Step 1: Create the Interim Table

Create a table ORDERS_INT that has the new column and the desired compression settings. It must have the same primary key as the original table.

CREATE TABLE APP_USER.ORDERS_INT (\n  ORDER_ID NUMBER PRIMARY KEY,\n  ORDER_DATE DATE,\n  CUSTOMER_ID NUMBER,\n  REGION_CODE VARCHAR2(10) -- The new column\n) COMPRESS ADVANCED;

Step 2: Start the Redefinition

Run this as a user with EXECUTE privileges on DBMS_REDEFINITION. This starts the data copy and creates the materialized view log on the source table.

BEGIN\n  DBMS_REDEFINITION.START_REDEF(\n    uname => 'APP_USER',\n    orig_table => 'ORDERS',\n    int_table => 'ORDERS_INT',\n    col_mapping => 'ORDER_ID ORDER_ID, ORDER_DATE ORDER_DATE, CUSTOMER_ID CUSTOMER_ID'\n  );\nEND;\n/

Step 3: Copy Dependents and Sync

You must copy indexes, constraints, and triggers from the original table to the interim table so they are ready for the swap.

DECLARE\n  num_errors PLS_INTEGER;\nBEGIN\n  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(\n    'APP_USER', 'ORDERS', 'ORDERS_INT', \n    DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, \n    num_errors\n  );\nEND;\n/

To reduce the final downtime window, synchronize the interim table. This applies the accumulated DML changes from the log to the interim table while the app is still running.

BEGIN\n  DBMS_REDEFINITION.SYNC_INTERIM_TABLE('APP_USER', 'ORDERS', 'ORDERS_INT');\nEND;\n/

Step 4: Finish the Redefinition

This is the only step that requires a brief exclusive lock. Oracle swaps the names of the tables and drops the materialized view log.

BEGIN\n  DBMS_REDEFINITION.FINISH_REDEF('APP_USER', 'ORDERS', 'ORDERS_INT');\nEND;\n/

Trade-offs and Resource Costs

Online redefinition is not \"free.\" It trades availability for resource consumption:

• Storage: You temporarily need enough space for two copies of the table.
• I/O and Redo: The process generates significant redo and undo logs because every row is being inserted into a new table. In high-traffic environments, this can lead to log file sync waits.
• Transaction Blocking: While FINISH_REDEF is fast, it can be delayed if there are very long-running transactions on the source table, as it needs a brief lock to finalize the swap.

Verification and Cleanup

After the process, verify the table structure and the presence of the new column:

DESC APP_USER.ORDERS;

Once you have confirmed the application is functioning correctly against the new structure, drop the interim table (which is now named ORDERS_INT but contains the old data) to reclaim space:

DROP TABLE APP_USER.ORDERS_INT;

Rollback Strategy

If you encounter errors before FINISH_REDEF, you can stop the process and clean up the logs using:

BEGIN\n  DBMS_REDEFINITION.ABORT_REDEF('APP_USER', 'ORDERS', 'ORDERS_INT');\nEND;\n/