In this Document

Applies to:

Purpose

The purpose of this bulletin is to give a broad overview of the use of DBMS_REDEFINITION to redefine tables while online (accessible to users for selects as well as insert/update/deletes etc).   

Scope

This bulletin is for experienced database administrators (DBAs).

Details

I) TASK OVERVIEW

DBMS_REDEFINITION is an extremely powerful package that is used to 'online redefine' tables

  Online redefinition of a table means that changes (inserts / updates / deletes / merges) can be made to the table while changes to the data are being made

DBMS_REDEFINITION uses Materialized View (MVIEW) technology in order to online redefine tables

DBMS_REDEFINITION.START_REDEF_TABLE -

* Initially instantiates the interim table as a fast refresh MVIEW
* Loads the data from the original table into the interim table
* A materialized view log is created on the original table if one does not already exist

DBMS_REDEFINITION.SYNC_INTERIM_TABLE - Performs a fast refresh on the MVIEW (original -> interim tables)

DBMS_REDEFINITION.FINISH_REDEF_TABLE - Peforms

Phase 1 - DBMS_REDEFINITION.SYNC_INTERIM_TABLE ... This brings the interim table as close to curent with the original as possible
Phase 2 - Locks the original table such that no changes can occur during the next phase
Phase 3 - DBMS_REDEFINITION.SYNC_INTERIM_TABLE ... This brings the interim table to curent with the original .. this phase should not take much time as phase 3 just 'synched' the tables
Phase 4 - The interim and original table names are swapped
Phase 5 - The MVIEW is unregistered and if this was the only fast refresh on the table the MVIEW log is dropped
Phase 6 -  The lock on the interim table (former original) is released

THINGS TO CONSIDER WHEN CHOOSING TO REDEFINE A TABLE ONLINE

1) Does the table need to be redefined online?

    Would doing the redefinition offline cause an unacceptable outage?
       if not ... then often it is faster to do the redefintion in a conventional (offline) manner
          (Create table as ... rename ... ALTER TABLE ... MOVE ... import/drop/export ... ETC)
       if so then online redefintion may be the only way to redefine the table

2) Is there sufficient storage space for the original and interim tables to exist at the same time?

   The interim and original tables must exist simultaneously until the redefinition is finished and the interim table is dropped ...
       as such .. there has to be sufficient space in the tablespace(s) to store both tables for this time

3) Online redefinition will often take longer ... sometimes much longer .. than doing a conventional redefinition

     This truly is not a concern as this process leaves the table being redefined accessible at all times except during the finish of the redefinition

This is because in actuality DBMS_REDEFINITION is doing similar operations to the conventional steps as well as the overhead of doing the redefinition online

4) The transaction rate at which the table that is being redefined needs to be considered

     If the transaction rate on the table is so high that the amount of time that consecutive synchronizations of the original and interim table take longer than
         an acceptable outage for the table then the either the transaction rate will need to be throttled before the last synchronization or the table will need to be
         redefined offline

     The reason why consideration needs to be given to the speed of synchronization is finishing the redefinition requires that the table be locked and then another
          synchronize is executed to make sure that all changes to the original table are made to the interim table .. before the tables are swapped

II) TASK EXAMPLES (HOW TO)

GENERAL STEPS TO ONLINE REDEFINE A TABLE

Examples Of The Use Of Dbms_redefinition

HOW TO SHRINK A SECUREFILE LOB USING ONLINE REDEFIITION (DBMS_REDEFINITION)? Document 1394613.1
HOW TO ADD (OR DROP) A COLUMN USING DBMS_REDEFINITION - Document 1120704.1
HOW TO MOVE A TABLE TO A NEW/DIFFERENT TABLESPACE WHILE IT IS ONLINE - Document 1357742.1
HOW TO SHRINK A TABLE USING ONLINE REDEFINITION Document 1357878.1
How to Compress a Table While it is Online Document 1353967.1
HOW TO ONLINE CONVERT TO/FROM SECUREFILES FROM/TO BASICFILES Document 728758.1
How to Disassemble (Uncluster / Decluster) Clustered Tables Online Document 807004.1
How to Convert Date Column to Timestamp Using DBMS_REDEFINITION Package? Document 556283.1
HOW TO USE DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT Document 1304838.1
How to convert LONG column to CLOB using package DBMS_REDEFINITION Document 251417.1
How To Partition Existing Table Using DBMS_Redefinition Document 472449.1
How To Use DBMS_REDEFINITION Package To Move All the IOTs And QUEUE TABLE To the New Tablespace Document 1296663.1
How To Change the Partition Column Of A Partitioned Table Using DBMS_Redefinition Document 846405.1
How To Encrypt/Rekey a Table for TDE and Avoid the Associated Performance Problems and Locks? Document 848298.1
How To Move SinglePartion Table To A New Tablespace? Document 795893.1
AN EXAMPLE OF A COMPLEX ONLINE TABLE REDEFINITION (DBMS_REDEFINITION) Document 1358236.1

III) TROUBLESHOOTING

KNOWN PROBLEMS

Cannot Drop Unused Columns Using dbms_redefinition Without PK And Compressed Table Document 1233204.1
WHY DOES DBMS_REDEFINITION NOT COPY NAMED LOBSEGMENT NAMES? Document 1237593.1
Reorganizing a Table with no Primary Key Using Dbms_redefinition Causes its Size to Increase Document 804575.1
WHY ARE 'NOT NULL' CONSTRAINTS NOT COPIED BY DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS? Document 1089860.1
ORA-1442 DURING DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS Document 1116785.1
ORA-904 DURING DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS Document 837751.1
Online Reorganization for Partition fails with ORA-39726 Document 462783.1
ORA-12091: Cannot Online Redefine Table When Running dbms_redefinition.start_redef_table Document 784086.1
Online Redefinition fails with ora-12015 when calling an external function Document 312010.1

References