Online redef - is there a better way?

From: Ind-dba <>
Date: Sat, 14 Feb 2009 05:32:58 -0800 (PST)
Message-ID: <>

We have a requirement to move some of the table from normal to partitioned.
I tested the oracle provided online redef way by simulating select+dml load on the table.

I tried using
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?

  • Sachin
Received on Sat Feb 14 2009 - 07:32:58 CST

Original text of this message