Wednesday, April 10, 2013


There is some confusion if dbms_redefinition can be used on a database where GoldenGate is configured.

First, let's see  what online Table Redefinition can be used for:

  • Add, remove, or rename columns from a table
  • Converting a non-partitioned table to a partitioned table and vice versa
  • Switching a heap table to an index organized and vice versa
  • Modifying storage parameters
  • Adding or removing parallel support
  • Reorganize (defragmenting) a table
  • Transform data in a table
Let's use these example tables:
Move EMP table to EMP_PARTITIONED_TEMP table

Here are the very high-level steps of online redefinition:
1) Create an interim table: CTAS EMP as EMP_PARTITIONED_TEMP (partitioned)

If GG DDL replication is enabled, then the interim table will also get replicated and created in the target.

2) Start redefinition - During this phase Oracle will copy (and partition) the data from the EMP to the EMP_PARTITIONED_TEMP. Oracle will also create a materialized view (snapshot) log on the table to track DML changes

At this point EMP's ObjID is 101 & EMP_PARTITIONED_TEMP's ObjID is 201
GG does not replicate any DDL executed by the dbms_redefinition pkg - so there is no mv snapshot taken on the target
The DML for both tables is replicated to the target (but this will be useless at the end)

3)  Finish redefinition - During this step Oracle will lock both tables in exclusive mode, swap the names of the two tables in the data dictionary, and enable all foreign key constraints

In this step, Oracle swaps the ObjIDs of the 2 tables, so EMP becomes 201, and EMP_PARTITIONED_TEMP becomes 101.
GG does not replicate any DDL executed by the dbms_redefinition pkg - so the swap of the ObjIDs is not replicated to the target

4) Remember to drop the original table afterwards. One can also consider renaming the constraints back to their original names (e.g.: alter table EMP rename constraint SYS_C001806 to emp_fk).

When the interim table is dropped, the 'drop table' is replicated to the target.  But since the ObjIDs were not replicated to the target, the interim table is dropped.  There was no affect of the redefinition on the target

No comments: