Re: Online redef - is there a better way?
Date: Sat, 14 Feb 2009 22:21:58 -0000
Message-ID: <AtSdnck2afeZ2QrUnZ2dnUVZ8rqdnZ2d_at_bt.com>
How many indexes do you have ?
You can resynch the table more than once in the redefinition process. Especially if there is a lot of extra activity, for example:
start redefinition (gets initial insert/select run)
resynch (with the log generated during the insert/select)
create first index nologging
resynch (with the log generated during index create)
create second index nologging
resynch,
repeat for each index in turn
each resynch will be slower as you have more indexes to maintain
finish redefinition.
The start redefinition and end redefinition both have to lock the table during the data dictionary updates - so it is possible that your end-user code gets an Oracle error 54 at either end of the redefinition process: it all depends on what your normal code does plus a little bit of luck.
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.com Author: Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html "Ind-dba" <oraclearora_at_googlemail.com> wrote in message news:d2463f1f-96cd-429e-ba94-d49a3c416f5e_at_p2g2000prf.googlegroups.com...Received on Sat Feb 14 2009 - 16:21:58 CST
>
> 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
>