Re: Migrate 200 Million of rows

I believe create table ... as select * from ... will cause all that data to go into rollback at one time unless autocommit is on. That will likely fail afer a lengthy attempt. Whatever process is used, peace-of-mind will be better if you can monitor the progress and have definate stop points that you can resume from. So, I would break it up somehow and use a one-time program to do the transfer. Nothing is worse that waiting three days for it to complete and then having to re-run it.

Good luck.

Nuno wrote:
> Hi,
>
> I would like to migrate 200 Million records. Furthermore my new table
> will have more fields that the old one. The new table will be in
> different database therefore a link will be used as well.
>
> I was thinking about:
> Strategy A)
> 1) create table ... as select * from
>
> with a join with temporary table to make the new table with new
> columns
>
> 2) create index, contraints etc
>
> Strategy B)
>
> 1) create table
>
> 2) create index and make it unusable (I have to check if this is
> possible in v8.0.5)
>
> 2) Insert into /*+ APPEND */ select * from
>
> 3) rebuild the index, constraints etc
>
> Could you please help me in the best strategy to perform this task?
>
> Do you have any more suggestions
Received on Thu Jul 24 2003 - 05:58:23 CDT