Re: fastest method to copy data between databases

David Pelinka wrote:
>
> I'm setting up an Oracle database for a data warehouse and would like to
> know what is the fastest way to move (replicate) data. I can't use
> snapshots against a remote view of multiple tables under 7.1.6 due to an
> Oracle bug. I've tried copy (copy from user_at_remote create local_table
> using select * from view) but it's taking 1 hour to move 1,000 - 10,000
> records. Table extents are few. The remote views are complex, but the
> select is fast in the remote database. Copying a single table is fast
> too.

When using SQL*Plus COPY command remember to set the environment vaiables
in SQL*Plus.

   set arraysize (defaults to 20 which is very small)

   set copycommit (defaults to end of copy; can be multiples of arraysize)

   set long (defaults to 80 which is too small)

   set longchunksize (defaults to 80 which is too small)

   set maxdata (set to maximum it will allow)

Having any of these too small will slow down your copy.

Settings will depend upon your row sizes and how big you can make maxdata.

If you are NOT using LONG fields then you can use a normal INSERT and database links but I think COPY is faster.

--
  -----------------
   T T T T T T T T        Bruce Pihlamae
   I I I I I I I I        bpihlama_at_nla.gov.au
   I I I I I I I I        National Library of Australia
   T T T T T T T T        Phone: +616 262-1575
  -----------------       Fax:   +616 273-2116
 ===================
 "If you swallow a live frog first thing in the morning;
  nothing worse will happen to either of you that day."