Re: Data extraction over dblink

Sandeep,

I'd second the comments about dblinks not scaling well. A while back I needed to do something like this to populate a data warehouse. I don't have accurate figures to hand, and it did not involve blobs, but I found spooling data to csv files, ftping them across the network and loading them with external tables to be of the order of 2-4 times faster than insert...select across a dblink.

Have you tried transportable tablespaces? I believe Oracle recommend them as the fastest transportation method, but of course, in 9i you are limited to both databases being on the same architecture. I'm not sure whether there are any restrictions in 10g.

Regards
David Lord

On 12/04/06, Sandeep Dubey <dubey.sandeep_at_gmail.com> wrote:
> I need to extract data from a remote database to local database. One
> of the column to extract is blob. Each single extract will fetch
> approx. 1 GB of data. Connectivity between the database is 10 mbps VPN
> connection over the internet. For security reasons Local database can
> have db link, ssh connectivity but not the other way. So it will be a
> pull mechanism from local and not push from remote to local.
>
> I am trying to compare the following approaches to extract data:
>
> 1. Extract data from remote database using dblink. Is it feasible to
> fetch 50K rows /1 GB data over a internet connection using db link? Is
> anyone using this approach?
>
> 2. Write the data to a temp table on the remote database. export and
> import the data in the local database.
>
> 3. Create a flat file for the data to be moved, transfer the file and
> load the file. I have a blob column. How difficult it is to write blob
> data along with other data to flat file and load using sqlloader?
>
> 4. Any other ideas?
>
>
> Thanks
>
> Sandeep
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l