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