Re: big table transfer, best way?

From: dwu <dwu_at_cn.oracle.com>
Date: 1997/05/13
Message-ID: <337804AD.6BB2_at_cn.oracle.com>#1/1


Kirill Richine wrote:
>
> I am wondering if there is a good way to transfer data from one
> schema to another, a slightly different one.
>
> Currently we try to do:
>
> insert into A_NEW (A_NEW_COL1, A_NEW_COL2)
> select A_OLD_COL1, A_OLD_COL2
> from A_OLD;
>
> This works fine for small tables. For big tables, however, there is a
> problem with the rollback segment size.
>
> We also tried separating the command into two parts like
>
> ... where rownum < 1000...
> commit
> ....
> ... where rownum >= 1000 ...
> etc
>
> but it does not seem to work.
>
> Thank you.
> k&

You can do it by two ways:
1. You can use a specified big rollback segment to execute the 'Insert' statement. For example:

    set transaction use rollback segment my_big_rollseg;     Insert into ...
 You have to note that the rollback segment must be enought big to hold the transcation.

2. You can use SQl*Loader to accomplish the task. It will be faster than to use 'Insert' statement. Received on Tue May 13 1997 - 00:00:00 CEST

Original text of this message