Re: What is the best way to copy 2G table data between two databases

I have lots of concerns in using db link. I don't know all the background technical details, but just my opinion it would be slower.

I must admit I have not timed a db link for something like this... I just never considered it.

I use exp/imp a LOT and have used the technique I listed below MANY times. - Note1: If you do not have fast local disk, then this would not work anywhere near as well as I have seen.
- Note2: If you do not have a fast network between the servers, then all these options are going to be slower, but it would be interesting to know the speed of the network between the servers. We currently have Gigbit Ethernet between our large Sun Servers.

It would be interesting to see which is quicker. Maybe you could do a test and tell everyone what you found :)

1. db link
2. exp/imp (being sure to use local disks, ftp over fast network, use local disk again, and no indexes on existing tables)
3. transfer table to a tablespace by itself and do the transportable tablespace option - If anything beats exp/imp, I'll bet this is it.

Thanks.

-- 

"David" <david_at_david.nospam.com> wrote in message
news:c0olbf$a8g2050_at_imsp212.netvigator.com...


> Hi Peltier,

>

>     Thanks for your info. Could you tell me why option 1 is better? Any

> concerns in using database link?

>

> Thanks,

> David

>

> "Burt Peltier" <burttemp1ReMoVeThIs_at_bellsouth.net> wrote in message

> news:tXOXb.634$fE4.238_at_bignews5.bellsouth.net...

> > Based on your info, I would choose option #1, but with a couple of

> comments:

> > - Do the export on server for database A to a local disk on this server.

> > - It would be best if the disk is fast and separate from online database

> > activity/files.

> > - Ftp the export dump file to server for database B, again to a good

> > performing local disk on server for database B.

> > - Run the import on server for database B .

> > - Make sure there are no indexes on the table during the import which is

> how

> > a normal import would run (creates table, inserts data, then does

> indexing).

> > - So, don't pre-create the table unless you make sure to drop indexes

also


> > (or just not create).

> >

> > --

> >

> > "David" <david_at_david.nospam.com> wrote in message

> > news:c0o8uh$a8717_at_imsp212.netvigator.com...

> > > Hi all,

> > >

> > >     I want to copy all data from a table (which is about 2G size) from

> > > database A to B (both are 8.1.7, archive log mode, but on different

> > server).

> > > What is the safest and fastest way to do this? I have some options:

> > >

> > > 1) exp/imp

> > > 2) insert /*+ append */ with nologging, with database link

> > >

> > >     Which option is better? Or any other ways?

> > >

> > > Thanks,

> > > David

> > >

> > >

> > >

> > >

> > >

> > >

> > >

> > >

> > >

> > >

> > >

> > >

> > >

> > >

> > >

> > >

> > >

> > >

> > >

> > >

> > >

> > >

> > >

> > >

> >

> >

>

>