How to Convert Oracle Tables Online Without Downtime Using DBMS_REDEFINITION

With increasing availability demands, changing a table definition in a 24/7 Oracle environment is challenging. Traditional methods require stopping the service, exporting data, dropping the old table, creating a new one, and re‑importing data, which can take hours. Oracle’s online redefinition feature, available since 9i via the DBMS_REDEFINITION package, enables table conversion with minimal lock time.

What Online Redefinition Does

It creates a duplicate target table, synchronizes all data from the source table to the target, then performs an incremental sync before swapping definitions. The final lock period is usually under one second, depending on the amount of incremental data.

Typical Use Cases

Convert between ordinary tables, partitioned tables, and index‑organized tables.

Move a table to a different tablespace.

Change storage attributes.

Rebuild a table to reduce fragmentation.

Advantages

Very small impact on business; lock time is only a brief instant.

Fast execution – a 15 GB table was redefined in 12 minutes in practice.

Disadvantages

Requires storage equal to the size of the original table (including indexes, LOBs, etc.).

Consumes additional system resources during the process.

Step‑by‑Step Procedure

Verify that the table can be redefined online (using primary key or ROWID method).

Create the target table structure (empty; indexes are not needed at this stage).

Start online redefinition and perform a full data sync.

Synchronize dependent objects such as indexes, constraints, triggers, and privileges.

Execute an incremental data sync.

Complete the online redefinition, which briefly locks the table and swaps metadata.

Drop the old table to free space.

Gather statistics, verify index names, parallelism, and recompile invalid objects.

Key DBMS_REDEFINITION Procedures

CAN_REDEF_TABLE

– checks if a table is eligible for redefinition. START_REDEF_TABLE – begins the redefinition process. COPY_TABLE_DEPENDENTS – copies dependent objects. SYNC_INTERIM_TABLE – performs incremental sync. FINISH_REDEF_TABLE – finishes and swaps the tables. ABORT_REDEF_TABLE – aborts and cleans up on error. REGISTER_DEPENDENT_OBJECTS / UNREGISTER_DEPENDENT_OBJECTS – manage dependent object registration.

Practical Example

In a real case, the table HJADM.REC_CODE_RESULT (≈5 GB) was converted to a partitioned table HJADM.REC_CODE_RESULT_TARGET. The steps followed the procedure above, with the full data sync taking about 10 minutes and the final lock lasting less than one second, resulting in virtually zero impact on the OLTP workload.

The experiment demonstrated that online redefinition is highly suitable for 7×24 OLTP systems, allowing continuous access, modifications, inserts, and deletes while the table structure changes.