Re: big table transfer, best way?

dwu wrote:

> 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.

 Well, I'd add a third method (and to me the simplest) - use a table-mode import/export. The FROMUSER and TOUSER clauses will help shift between schemas.

Steve Phelan. Received on Tue May 13 1997 - 00:00:00 CEST