Re: big table transfer, best way?

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 ;