Online redef - is there a better way?
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