Oracle table transfer between databases in two different hosts I have a 500Gb table which I need to transfer to another Oracle database in another remote host. I have a 10 Megabits/sec speed connection between two hosts. What can be the most efficient way to transfer the table? With minimal chance of disaster.

There shouldn't be any data loss even with fluctuating connection. The method must be fastest among all.

I want a solution without using external drives.
Use the SneakerNet

The SneakerNet may be old, but it is still useful for some extreme cases like yours.


Buy a 1 TB hard drive
Create a Transportable Table Space (TTS) or Dump file (expdp) on the HD
Manually transfer the hard drive.


USPS/UPS/FedEx also works

Load it onto remote server


EDIT

The below section was added due to the comment from the original poster.

Still want to use your internet to transfer?

Increase the bandwidth at both sites.

This is extremely location specific.  If you can, this would be recommended.
I believe 4G LTE (cell phones) can transfer at rates that are faster than 10 Mbps.

Maybe, you are at some extreme location that makes SneakNet unviable (eg 10 day hike in the amazon).  Or, that is the fastest speed available for any ISP.

Extreme example: you are trying to transfer 500GB of data from Opportunity and its modem has a transfer rate of 10Mbps.

I'm assuming you have some sort of restriction due to your terrestrial position.

Data can be made READ ONLY

The READ ONLY requirement is to ensure that the data does not change over the 5 - 10 days it will take to copy everything


Create a database link
Use DBMS_PARALLEL_EXECUTE (DPE) with a PARALLEL_LEVEL =&gt; 1


The SQL statement you will use is a simple INSERT ... SELECT statement. (with the appropriate WHERE clause for DPE)



Your bandwith is going to be maxed out for those 5+ days.

You will need to adjust the chunk size to match some personal preference for "ok, I can wait x minutes to redo this CHUNK that failed".

DPE is used to help you easily manage those failed chunks.

Table is part of an OLTP

In this scenario, the table is guaranteed to change throughout the 10+ days (2* calculated) to transfer the data.

Your OS of choice could be your blessing or your curse.

Also, you will need a lot of disk space.  A lot more than 500GB (more like 1.5TB to 2TB).

Similar to DPE, you break up the data into smaller, more manageable chunks.  However, you need to do all of the work.

Steps involved


create expdp file (oracle documentation)
compress it. (OS specific)


this might reduce your data. Your Millage May Vary (YMMV)

split file into multiple chunks (OS Specific)
gather an md5/sha-256 checksum on all of the pieces (OS Specific)
ftp each chunk (OS Specific)


restart the FTP as necessary
make sure you are ready to wait 5+ days.
if internet connection is unreliable, write a shell script to automate the process (OS Specific)
you are probably using a shared connection.  Plan for 10+ days

gather the md5/sha-256 checksum for all of the pieces on the remote site (OS Specific)
compare (OS Specific)


resend missing pieces that are missing
resend pieces whose MD5/sha-256 checksum do not match.
you did put the FTP in BINARY mode first.  right?

join the files together (OS Specific)
uncompress the file(OS Specific)
impdp the file (oracle documentation)


The low end estimate of 115 hours is based on the given uncompressed data size.  If your data can be compressed, you can see significant reduction in size. Your mileage may vary (YMMV).