Re: Connection dropped on long remote DB link

On 18 Jul 2001, gregoryk_at_futures.wharton.upenn.edu wrote:

> Hello, We are using a remote DB link to connect to a machine
> far far away in Nebraska and then performing a
>
> insert into localtable
> select * from remotetable
> where certainstuff = otherstuff
>
> query to get certain data from the remote machine locally to
> perform some analysis on it. The only problem is that our db
> link connections get dropped with some frequency requiring us
> to restart the transfer process.

I wonder if the transfer process has started yet, or is the connection timing out based on no traffic?

> Also, the transfer process itself takes several hours (too
> long).

QUESTIONS: Can you find out how long the query takes to run on that server as a local process? This might give you a better clue as to how much the network is a bottleneck.

How big is the dataset you are trying to insert?

How long does it take to insert just one row, then how but 100 rows?

Is the query using an index? (Though, unless the table is just out of this world big, a full table scan ain't going to take a few hours, so the network is definitely causing some kind of performance bottlenecks)

> Does anyone have advice on 1)What could be causing the DB link
> to get dropped or what to do to maket it more stable?
>
> and/or
>
> 2)What (cheap) method we could use to transport this data? Most
> ETL tools seem to be out of the range of this project since the
> import of this data is just a relatively small part of our
> whole process.

Can you connect to that machine or do you not even know the connection parameters and are only given the remotetable as something to query? If you can, maybe you could telnet to the machine and run sqlplus and spool to a file on that server which you then retrieve by ftp and then upload by sqlldr? You would have to know when the process has completed on the other machine though.

-- 
Galen Boyer
It seems to me, I remember every single thing I know.