RE: Large data movement to new partitioned table
- From: "Knight, Jon" <jknight@xxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 19 Oct 2005 13:45:39 -0500
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@xxxxxxxxxxxxxxxxxx [mailto:tboss@xxxxxxxxxxxxxxxxxx]
Sent: Miércoles, 19 de Octubre de 2005 01:00 p.m.
To: jknight@xxxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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;
> /
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
