Online redef - is there a better way?
Date: Sat, 14 Feb 2009 05:32:58 -0800 (PST)
We have a requirement to move some of the table from normal to
I tested the oracle provided online redef way by simulating select+dml load on the table.
I tried using
exec DBMS_REDEFINITION.START_REDEF_TABLE ('OWNER1','SOURCE1','SOURCE1_TEMP$')
In this SOURCE1 is non partition table and SOURCE1_TEMP$ is hash partition table on one of the keys.
This finished rather quickly - in 1 hr .. the no of rows in source1 table is 40 million.
After the above finished i created the necessary indexes/constraints.
-- this took 1 more hour. In parallel select and DML (upload) load was
After index+constraints -- i used: exec
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('OWNER1','SOURCE1','SOURCE1_TEMP $') -- this ran almost like for 8 hrs before i aborted this.
It seems online redef uses Mviews to refresh the data.
My question is - Is there a better way to move non-part tables to part ways in a real OLTP environment or any extra precaution to make DBMS_REDEFINITION work seamless?