Re: big table transfer, best way?

Selvamani 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 use
> create table a_new as
> select A_OLD_COL1, A_OLD_COL2
> from A_OLD;
>
> or
>
> you can use pl/sql as follows.
>
> declare
> cursor c1 is
> select A_OLD_COL1, A_OLD_COL2
> from A_OLD;
> begin
> for c1_rec in c1
> loop
> insert into a_new values (c1_rec.a_old_col1, c1_rec.a_old_col2);
> commit ;
> end loop ;
> end ;
> /

You can use the COPY command instead of INSERT into SELECT.

In COPY command you can set the buffer size ORACLE will commit after each buffer. With help of COPY you can transfer the large data from one table to another table with low rollback segment size. In manual it is explained this well.

Vijay Darekar Received on Thu May 15 1997 - 00:00:00 CEST