Re: Reg Partition Exchange

From: <pankaj_wolfhunter_at_yahoo.co.in>
Date: 29 Jun 2006 22:21:52 -0700
Message-ID: <1151644912.397745.181330@m73g2000cwd.googlegroups.com>

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 Morgan and Sorry for late reply.
Your idea of APPEND hint really helped in reducing time. Actually implementation for the same took some time and was not able to reply to you soon.
Thanks again Received on Fri Jun 30 2006 - 00:21:52 CDT