Re: Online redef - is there a better way?

From: Jonathan Lewis <>
Date: Sat, 14 Feb 2009 22:21:58 -0000
Message-ID: <>

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

    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.


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ

"Ind-dba" <> wrote in message

> 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
> running.
> After index+constraints -- i used: exec
> $') -- 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 - 16:21:58 CST

Original text of this message