Large data movement to new partitioned table

From: Knight, Jon <jknight_at_concordefs.com>
Date: Wed, 19 Oct 2005 11:15:04 -0500
Message-ID: <17ECCBDCF27C544583F2CAD928F953260221FD2F@memex1.corp.cefs.int>

  I have a couple of large tables (average 350,000 recs/day) that I need to move to a different schema. We have almost 1 year of data. They are currently traditional table structures and I'm moving them to partitioned tables. The table names are different between schemas.

  We have already created the partitioned tables. I'm wondering if export/import will do the trick. Will the records go into the correct partitions? What about the different table names? I had planned to use pl/sql, but thought I'd ask for other suggestions first. If you don't have an alternative suggestion, could you please comment on whether there is a more efficient(quicker) pl/sql approach than what I have below. Particularly, I'm interested in avoiding rollback errors.

  We're still on Oracle 8i, Sun Solaris.

Many thanks,
Jon Knight

declare
  cursor c1 is
    select ...
    from schema1.old_table_name
    ;

  v1                          c1%rowtype;

begin
  open c1;
  loop
    fetch c1 into v1;
    exit when c1%notfound;

    insert into schema2.new_table_name (...)     values (...);

    commit;
  end loop;
  close c1;

end;
/

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Oct 19 2005 - 11:19:42 CDT