Re: Reg Partition Exchange

On 27 Jun 2006 09:09:48 -0700, pankaj_wolfhunter_at_yahoo.co.in wrote:

>
>DA Morgan wrote:
>
>> pankaj_wolfhunter_at_yahoo.co.in wrote:
>> > DA Morgan wrote:
>> >> pankaj_wolfhunter_at_yahoo.co.in wrote:
>> >>> Greetings,
>> >>> We have a requirement where we want to transfer some
>> >>> millions of data in 15-16 tables from one schema to same structure
>> >>> tables in another. Both source and destination tables are partitioned.
>> >>> Right now we are going for partition exchange for both schemas. But as
>> >>> we know partition-exchange empties the source table from where it is
>> >>> tranferring the data.
>> >>> For that we are taking help of temporary tables in between but it is
>> >>> really causing major performance problems. The whole process goes
>> >>> beyond 3-4 hrs.
>> >>> Just want to know is there any way in partition-exchange where we can
>> >>> directly transfer the data between two tables without loosing data Or
>> >>> any other way some one can suggest?
>> >>>
>> >>> We are using Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
>> >>>
>> >>> Any help would be greatly appreciated.
>> >>>
>> >>> TIA
>> >> Two schemas in a single database?
>> >> Two different databases?
>> >> --
>> >> Daniel A. Morgan
>> >> University of Washington
>> >> damorgan_at_x.washington.edu
>> >> (replace x with u to respond)
>> >> Puget Sound Oracle Users Group
>> >> www.psoug.org
>> >
>> > Two schemas in a single database.
>>
>> Why not CTAS (CREATE TABLE AS) with the APPEND hint?
>> --
>> Daniel A. Morgan
>> University of Washington
>> damorgan_at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Group
>> www.psoug.org
>
>Thanks for the reply.
>Actually people are using old days data from the partition tables.
>After we load the new date's data the users are informed about the
>same.
>Is there any other way?

Hint: CTAS means
Create table ....
as
select

He wants you to
create table ...
as
select
/* +append */
from ....

--
Sybrand Bakker, Senior Oracle DBA