Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Large data movement to new partitioned table
insert into ... select ... seems to be working fine. I'm doing a month at
a time. Thanks to all for the help.
Thanks,
Jon Knight
-----Original Message-----
From: tboss_at_bossconsulting.com [mailto:tboss_at_bossconsulting.com] Sent: Miércoles, 19 de Octubre de 2005 01:00 p.m. To: jknight_at_concordefs.com Cc: oracle-l_at_freelists.org Subject: Re: Large data movement to new partitioned table
350k records a day, so about 127M records in a year? You can create table as select ... from your old table and probably have your new table done in about an hour and a half.
Easiest way to go.
>
> 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
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Oct 19 2005 - 13:49:40 CDT
![]() |
![]() |