Re: copy data between instances

bdg_at_hotmail.com wrote:
>
> Thanks for bringing that up. I experimented with database links and
> found that I could not get parallel performance improvements from my
> queries. My tests used the "create table as select" with the
> unrecoverable clause. For example
>
> CREATE TABLE NEW_TABLE
> PARALLEL ( DEGREE 4 )
> UNRECOVERABLE
> AS
> SELECT *
> FROM OLD_TABLE_at_SOURCE;
>
> Copying a table inside a single instance, I was able to significantly
> improve performance by including the parallel clause (from ~90 seconds
> to ~15 seconds for a 30MB table). But when performing the same
> statement on a remote table, the parallel clause had no affect on
> performance. The Oracle Support site confirmed that the parallel clause
> was ignored when a database link was used (at least in 7.3 and 8.0,
> which are the only two I'm interested in now). I also tried to
> simultaneously copy three individual ~30MB tables between databases
> across a database link to see if I create parallelism that way, but
> repeatedly found that one table took ~90 seconds, the second took ~180
> seconds and the third took ~270 seconds, leading me to believe that I
> would never be able to get parallel performance through a database link.
> Perhaps someone can offer suggestions to improve on this situation. Of
> course this option would still omit the benefits of direct path loading.
>
> Both the source and target databases are currently 7.3.4. Eventually
> the target database will be 8.0.6.
>
> Brian Gastineau
> Gulf Coast Regional Blood Center
>
> In article <971162735.121783_at_zenana.dcz.bekaert.com>,
> "Thierry Poels" <Thierry.Poels_at_nospam> wrote:
> > Hi,
> >
> > how about a simple database link ??
> > You'll be able to access the tables in the other database (more or
 less)
> > like any local table.
> >
> > regards,
> > Thierry Poels
> > HP-UX Sysadmin & Oracle DBA.
> >
> > bdg_at_hotmail.com wrote in message <8rthfj$phj$1_at_nnrp1.deja.com>...
> > >Do any utilities exist that would provide SQL*Loader performance, but
> > >use another Oracle database (on same host) as the data source instead
 of
> > >a text file? Essentially I would like the basic export/import
> > >functionality, without having to create an interim dump file. Import
> > >also seems to be lacking parallel and direct path performance
 features.
> > >
> > >Brian Gastineau
> > >Gulf Coast Regional Blood Center
> > >
> > >
> > >Sent via Deja.com http://www.deja.com/
> > >Before you buy.
> >
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

Try putting your parallel clause in your SELECT rather than the create and see if you get parallel servers running on the remote node...

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk
(faster/mirrored at http://www.oradba.freeserve.co.uk)

Its not the voices in my head that bother me... 
  its the voices in yours.