Online redef - is there a better way?

From: Ind-dba <oraclearora_at_googlemail.com>
Date: Sat, 14 Feb 2009 05:32:58 -0800 (PST)
Message-ID: <d2463f1f-96cd-429e-ba94-d49a3c416f5e_at_p2g2000prf.googlegroups.com>


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
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
running.

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